package de.arxsilex.maptools.converter.worker

import java.nio.file.Path
import java.sql.Connection
import java.sql.DriverManager
import de.arxsilex.maptools.converter.core.PoiSchemaVersion

/** Converts the current Mapsforge POI v4 storage into the official v3 table layout. */
internal object MapsforgePoiSchemaConverter {
    fun convert(path: Path, source: PoiSchemaVersion, target: PoiSchemaVersion) {
        if (source == target) return
        when (source to target) {
            PoiSchemaVersion.V3 to PoiSchemaVersion.V4 -> convertV3ToV4(path)
            PoiSchemaVersion.V4 to PoiSchemaVersion.V3 -> convertV4ToV3(path)
            else -> error("POI-Konvertierung von v${source.metadataVersion} nach v${target.metadataVersion} wird nicht unterstützt.")
        }
    }

    fun convertV3ToV4(path: Path) {
        Class.forName("org.sqlite.JDBC")
        DriverManager.getConnection("jdbc:sqlite:${path.toAbsolutePath()}").use { connection ->
            val sourceCount = connection.singleLong("SELECT COUNT(*) FROM poi_index")
            connection.autoCommit = false
            try {
                connection.createStatement().use { statement ->
                    statement.execute("CREATE TEMP TABLE poi_positions (id INTEGER PRIMARY KEY, lat REAL, lon REAL)")
                    statement.execute("INSERT INTO poi_positions SELECT id, lat, lon FROM poi_index")
                    statement.execute("DROP TABLE poi_index")
                    statement.execute(
                        "CREATE VIRTUAL TABLE poi_index USING rtree(id, minLat, maxLat, minLon, maxLon)",
                    )
                    statement.execute(
                        "INSERT INTO poi_index (id, minLat, maxLat, minLon, maxLon) " +
                            "SELECT id, lat, lat, lon, lon FROM poi_positions",
                    )
                    statement.execute("DROP TABLE IF EXISTS poi_data_fts")
                    statement.execute(
                        "CREATE VIRTUAL TABLE poi_data_fts " +
                            "USING fts5(data, content='poi_data', content_rowid='id')",
                    )
                    statement.execute("INSERT INTO poi_data_fts(poi_data_fts) VALUES ('rebuild')")
                    statement.execute("CREATE INDEX IF NOT EXISTS poi_category_map_idx ON poi_category_map (category)")
                    statement.execute("UPDATE metadata SET value = '4' WHERE name = 'version'")
                    statement.execute(
                        "UPDATE metadata SET value = 'MapTools Converter (Mapsforge POI v4)' WHERE name = 'writer'",
                    )
                }
                val targetCount = connection.singleLong("SELECT COUNT(*) FROM poi_index")
                require(targetCount == sourceCount) {
                    "Beim POI-v4-Kompatibilitätsexport gingen Indexeinträge verloren ($sourceCount/$targetCount)."
                }
                connection.commit()
            } catch (exception: Exception) {
                connection.rollback()
                throw exception
            } finally {
                connection.autoCommit = true
            }
            connection.createStatement().use { statement -> statement.execute("VACUUM") }
        }
    }

    fun convertV4ToV3(path: Path) {
        Class.forName("org.sqlite.JDBC")
        DriverManager.getConnection("jdbc:sqlite:${path.toAbsolutePath()}").use { connection ->
            val sourceCount = connection.singleLong("SELECT COUNT(*) FROM poi_index")
            connection.autoCommit = false
            try {
                connection.createStatement().use { statement ->
                    statement.execute(
                        "CREATE TABLE poi_index_v3 " +
                            "(id INTEGER PRIMARY KEY, lat REAL NOT NULL, lon REAL NOT NULL)",
                    )
                    statement.execute(
                        "INSERT INTO poi_index_v3 (id, lat, lon) " +
                            "SELECT id, (minLat + maxLat) / 2.0, (minLon + maxLon) / 2.0 FROM poi_index",
                    )
                    statement.execute("DROP TABLE poi_index")
                    statement.execute("ALTER TABLE poi_index_v3 RENAME TO poi_index")
                    statement.execute("CREATE INDEX poi_index_idx_lat ON poi_index (lat)")
                    statement.execute("CREATE INDEX poi_index_idx_lon ON poi_index (lon)")
                    statement.execute("DROP TABLE IF EXISTS poi_data_fts")
                    statement.execute("DROP INDEX IF EXISTS poi_category_map_idx")
                    statement.execute("UPDATE metadata SET value = '3' WHERE name = 'version'")
                    statement.execute(
                        "UPDATE metadata SET value = 'MapTools Converter (Mapsforge POI v3)' WHERE name = 'writer'",
                    )
                }
                val targetCount = connection.singleLong("SELECT COUNT(*) FROM poi_index")
                require(targetCount == sourceCount) {
                    "Beim POI-v3-Kompatibilitätsexport gingen Indexeinträge verloren ($sourceCount/$targetCount)."
                }
                connection.commit()
            } catch (exception: Exception) {
                connection.rollback()
                throw exception
            } finally {
                connection.autoCommit = true
            }
            connection.createStatement().use { statement -> statement.execute("VACUUM") }
        }
    }

    private fun Connection.singleLong(sql: String): Long =
        createStatement().use { statement ->
            statement.executeQuery(sql).use { result ->
                check(result.next()) { "Die POI-Datenbankabfrage lieferte kein Ergebnis." }
                result.getLong(1)
            }
        }
}
