package de.arxsilex.maptools.converter.worker

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

internal data class PoiFilterReport(val sourceCount: Long, val selectedCount: Long)

/** Filters a copied Mapsforge POI database without reconstructing or deduplicating its POI data. */
internal object MapsforgePoiSpatialFilter {
    fun filter(path: Path, schema: PoiSchemaVersion, areas: List<GeoSelectionRegion>): PoiFilterReport {
        require(areas.isNotEmpty()) { "Mindestens ein POI-Auswahlbereich ist erforderlich." }
        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 selected_poi_ids (id INTEGER PRIMARY KEY)")
                }
                val selectSql = when (schema) {
                    PoiSchemaVersion.V3 -> "SELECT id, lat, lon FROM poi_index"
                    PoiSchemaVersion.V4 ->
                        "SELECT id, (minLat + maxLat) / 2.0, (minLon + maxLon) / 2.0 FROM poi_index"
                }
                connection.prepareStatement("INSERT INTO selected_poi_ids (id) VALUES (?)").use { insert ->
                    connection.createStatement().use { statement ->
                        statement.executeQuery(selectSql).use { result ->
                            var batchSize = 0
                            while (result.next()) {
                                val latitude = result.getDouble(2)
                                val longitude = result.getDouble(3)
                                if (areas.any { it.contains(latitude, longitude) }) {
                                    insert.setLong(1, result.getLong(1))
                                    insert.addBatch()
                                    batchSize++
                                    if (batchSize >= INSERT_BATCH_SIZE) {
                                        insert.executeBatch()
                                        batchSize = 0
                                    }
                                }
                            }
                            if (batchSize > 0) insert.executeBatch()
                        }
                    }
                }
                connection.createStatement().use { statement ->
                    if (schema == PoiSchemaVersion.V4) statement.execute("DROP TABLE IF EXISTS poi_data_fts")
                    statement.execute(
                        "DELETE FROM poi_category_map WHERE NOT EXISTS " +
                            "(SELECT 1 FROM selected_poi_ids s WHERE s.id = poi_category_map.id)",
                    )
                    statement.execute(
                        "DELETE FROM poi_data WHERE NOT EXISTS " +
                            "(SELECT 1 FROM selected_poi_ids s WHERE s.id = poi_data.id)",
                    )
                    statement.execute(
                        "DELETE FROM poi_index WHERE NOT EXISTS " +
                            "(SELECT 1 FROM selected_poi_ids s WHERE s.id = poi_index.id)",
                    )
                    val bounds = SelectionBoundsCodec.envelope(areas)
                    val boundsValue = "${bounds.south},${bounds.west},${bounds.north},${bounds.east}"
                    connection.prepareStatement("UPDATE metadata SET value = ? WHERE name = 'bounds'").use { update ->
                        update.setString(1, boundsValue)
                        update.executeUpdate()
                    }
                    if (schema == PoiSchemaVersion.V4) {
                        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)")
                    }
                }
                val selectedCount = connection.singleLong("SELECT COUNT(*) FROM poi_data")
                require(selectedCount == connection.singleLong("SELECT COUNT(*) FROM poi_index")) {
                    "POI-Daten und räumlicher Index sind nach dem Zuschneiden unvollständig."
                }
                connection.commit()
                connection.autoCommit = true
                connection.createStatement().use { it.execute("VACUUM") }
                return PoiFilterReport(sourceCount, selectedCount)
            } catch (exception: Exception) {
                connection.rollback()
                throw exception
            } finally {
                if (!connection.autoCommit) connection.autoCommit = true
            }
        }
    }

    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)
            }
        }

    private const val INSERT_BATCH_SIZE = 4_096
}
