package de.arxsilex.maptools.converter.desktop

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

class PoiDirectConversionIntegrationTest {
    @Test
    fun `worker converts a complete poi database from version three to four`() {
        val directory = createTempDirectory("poi-direct-conversion")
        val input = directory.resolve("source.poi")
        val output = directory.resolve("target.poi")
        try {
            Class.forName("org.sqlite.JDBC")
            DriverManager.getConnection("jdbc:sqlite:${input.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, lon REAL)")
                    statement.execute(
                        "INSERT INTO metadata VALUES " +
                            "('version', '3'), ('bounds', '50.0,8.0,51.0,9.0'), ('writer', 'test')",
                    )
                    statement.execute("INSERT INTO poi_categories VALUES (1, 'amenity', NULL)")
                    statement.execute("INSERT INTO poi_data VALUES (7, 'amenity=cafe\nname=Café Waldblick')")
                    statement.execute("INSERT INTO poi_category_map VALUES (7, 1)")
                    statement.execute("INSERT INTO poi_index VALUES (7, 50.5, 8.5)")
                }
            }

            WorkerLauncher.convertPoi(
                input = input,
                output = output,
                sourceSchema = PoiSchemaVersion.V3,
                targetSchema = PoiSchemaVersion.V4,
                temporaryDirectory = directory.resolve("temp"),
                logDirectory = directory.resolve("logs"),
            )

            ExportOutputValidator.validateMapsforgePoi(output, PoiSchemaVersion.V4)
            assertEquals(4, MapsforgePoiSource.readMetadata(output).schemaVersion)
            assertEquals("Café Waldblick", PreviewSearch.findPoiDatabases(
                listOf(
                    PreviewDetailSource(
                        input = de.arxsilex.maptools.converter.core.InputFileInfo(
                            input.toString(), input.fileName.toString(), Files.size(input),
                            de.arxsilex.maptools.converter.core.InputFormat.MAPSFORGE_POI,
                        ),
                        mapPath = input,
                        poiPath = output,
                    ),
                ),
                "Cafe Wald",
            ).single().text)
        } finally {
            directory.toFile().deleteRecursively()
        }
    }

    @Test
    fun `worker crops original poi data once across overlapping regions and converts schema`() {
        val directory = createTempDirectory("poi-spatial-conversion")
        val input = directory.resolve("source.poi")
        val output = directory.resolve("target.poi")
        try {
            Class.forName("org.sqlite.JDBC")
            DriverManager.getConnection("jdbc:sqlite:${input.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, lon REAL)")
                    statement.execute(
                        "INSERT INTO metadata VALUES " +
                            "('version', '3'), ('bounds', '50.0,8.0,51.0,9.0'), ('writer', 'test')",
                    )
                    statement.execute("INSERT INTO poi_categories VALUES (1, 'amenity', NULL)")
                    statement.execute(
                        "INSERT INTO poi_data VALUES " +
                            "(7, 'name=Innen Eins'), (8, 'name=Innen Zwei'), (9, 'name=Außen')",
                    )
                    statement.execute("INSERT INTO poi_category_map VALUES (7, 1), (8, 1), (9, 1)")
                    statement.execute(
                        "INSERT INTO poi_index VALUES " +
                            "(7, 50.3, 8.3), (8, 50.5, 8.5), (9, 50.9, 8.9)",
                    )
                }
            }

            WorkerLauncher.convertPoi(
                input = input,
                output = output,
                sourceSchema = PoiSchemaVersion.V3,
                targetSchema = PoiSchemaVersion.V4,
                areas = listOf(
                    GeoRectangleRegion(GeoBounds(8.0, 50.0, 8.4, 50.4)),
                    GeoCircleRegion(centerLatitude = 50.5, centerLongitude = 8.5, radiusKilometers = 30.0),
                ),
                temporaryDirectory = directory.resolve("temp"),
                logDirectory = directory.resolve("logs"),
            )

            ExportOutputValidator.validateMapsforgePoi(output, PoiSchemaVersion.V4)
            val metadata = MapsforgePoiSource.readMetadata(output)
            assertEquals(2L, metadata.poiCount)
            DriverManager.getConnection("jdbc:sqlite:${output.toAbsolutePath()}").use { connection ->
                connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT data FROM poi_data ORDER BY id").use { result ->
                        val names = buildList { while (result.next()) add(result.getString(1)) }
                        assertEquals(listOf("name=Innen Eins", "name=Innen Zwei"), names)
                    }
                }
            }
        } finally {
            directory.toFile().deleteRecursively()
        }
    }
}
