package de.arxsilex.maptools.converter.worker

import java.nio.file.Files
import java.sql.DriverManager
import kotlin.io.path.createTempFile
import kotlin.test.Test
import kotlin.test.assertEquals
import kotlin.test.assertFalse
import kotlin.test.assertTrue

class MapsforgePoiSchemaConverterTest {
    @Test
    fun `converts version four indexes to version three without losing pois`() {
        val file = createTempFile(suffix = ".poi")
        try {
            Files.delete(file)
            Class.forName("org.sqlite.JDBC")
            DriverManager.getConnection("jdbc:sqlite:${file.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 INDEX poi_category_map_idx ON poi_category_map (category)")
                    statement.execute("CREATE TABLE poi_data (id INTEGER PRIMARY KEY, data TEXT)")
                    statement.execute("CREATE VIRTUAL TABLE poi_data_fts USING fts5(data, content='poi_data', content_rowid='id')")
                    statement.execute("CREATE VIRTUAL TABLE poi_index USING rtree(id, minLat, maxLat, minLon, maxLon)")
                    statement.execute("INSERT INTO metadata VALUES ('version', '4'), ('writer', 'mapsforge')")
                    statement.execute("INSERT INTO poi_data VALUES (7, 'name=Test')")
                    statement.execute("INSERT INTO poi_index VALUES (7, 50.1, 50.1, 8.2, 8.2)")
                    statement.execute("INSERT INTO poi_data_fts(poi_data_fts) VALUES ('rebuild')")
                }
            }

            MapsforgePoiSchemaConverter.convertV4ToV3(file)

            DriverManager.getConnection("jdbc:sqlite:${file.toAbsolutePath()}").use { connection ->
                val tables = connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT name FROM sqlite_master WHERE type='table'").use { result ->
                        buildSet { while (result.next()) add(result.getString(1)) }
                    }
                }
                assertTrue("poi_index" in tables)
                assertFalse("poi_data_fts" in tables)
                connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT id, lat, lon FROM poi_index").use { result ->
                        assertTrue(result.next())
                        assertEquals(7L, result.getLong("id"))
                        assertEquals(50.1, result.getDouble("lat"), 0.00001)
                        assertEquals(8.2, result.getDouble("lon"), 0.00001)
                    }
                    statement.executeQuery("SELECT value FROM metadata WHERE name='version'").use { result ->
                        assertTrue(result.next())
                        assertEquals("3", result.getString(1))
                    }
                }
            }

            MapsforgePoiSchemaConverter.convertV3ToV4(file)

            DriverManager.getConnection("jdbc:sqlite:${file.toAbsolutePath()}").use { connection ->
                val tables = connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT name FROM sqlite_master WHERE type='table'").use { result ->
                        buildSet { while (result.next()) add(result.getString(1)) }
                    }
                }
                assertTrue("poi_data_fts" in tables)
                connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT id, minLat, maxLat, minLon, maxLon FROM poi_index").use { result ->
                        assertTrue(result.next())
                        assertEquals(7L, result.getLong("id"))
                        assertEquals(50.1, result.getDouble("minLat"), 0.00001)
                        assertEquals(50.1, result.getDouble("maxLat"), 0.00001)
                        assertEquals(8.2, result.getDouble("minLon"), 0.00001)
                        assertEquals(8.2, result.getDouble("maxLon"), 0.00001)
                    }
                    statement.executeQuery("SELECT value FROM metadata WHERE name='version'").use { result ->
                        assertTrue(result.next())
                        assertEquals("4", result.getString(1))
                    }
                    statement.executeQuery("SELECT COUNT(*) FROM poi_data_fts WHERE poi_data_fts MATCH 'Test'").use { result ->
                        assertTrue(result.next())
                        assertEquals(1L, result.getLong(1))
                    }
                }
            }
        } finally {
            Files.deleteIfExists(file)
        }
    }
}
