package de.arxsilex.maptools.converter.desktop

import de.arxsilex.maptools.converter.core.GeoBounds
import de.arxsilex.maptools.converter.core.MapsforgePoiMetadata
import de.arxsilex.maptools.converter.core.PoiSchemaVersion
import de.arxsilex.maptools.converter.core.preview.PreviewDataset
import de.arxsilex.maptools.converter.core.preview.PreviewFeatureClass
import de.arxsilex.maptools.converter.core.preview.PreviewLabel
import de.arxsilex.maptools.converter.core.preview.PreviewLabelClass
import de.arxsilex.maptools.converter.core.preview.PreviewPoint
import java.nio.file.Files
import java.nio.file.Path
import java.sql.Connection
import java.sql.DriverManager

internal object MapsforgePoiSource {
    fun readMetadata(path: Path): MapsforgePoiMetadata {
        require(Files.isRegularFile(path)) { "POI-Datei nicht gefunden: $path" }
        Class.forName("org.sqlite.JDBC")
        val databaseUri = path.toAbsolutePath().toUri()
        DriverManager.getConnection("jdbc:sqlite:$databaseUri?mode=ro").use { connection ->
            val version = connection.metadataValue("version")?.toIntOrNull()
                ?: error("In der Mapsforge-POI-Datei fehlt eine gültige Schemaversion.")
            val schema = PoiSchemaVersion.entries.singleOrNull { it.metadataVersion == version }
                ?: error("Mapsforge-POI-Schema v$version wird nicht unterstützt.")
            val boundsValues = connection.metadataValue("bounds")
                ?.split(',')
                ?.mapNotNull(String::toDoubleOrNull)
                ?.takeIf { it.size == 4 }
                ?: error("In der Mapsforge-POI-Datei fehlen gültige Kartengrenzen.")
            val bounds = GeoBounds(
                west = boundsValues[1],
                south = boundsValues[0],
                east = boundsValues[3],
                north = boundsValues[2],
            )
            ExportOutputValidator.validateMapsforgePoi(path, schema)
            return MapsforgePoiMetadata(
                bounds = bounds,
                schemaVersion = version,
                poiCount = connection.singleLong("SELECT COUNT(*) FROM poi_data"),
                writer = connection.metadataValue("writer"),
            )
        }
    }

    fun loadPreview(path: Path, bounds: GeoBounds, limit: Int = 60_000): PreviewDataset {
        require(limit > 0) { "Das POI-Vorschaulimit muss größer als null sein." }
        val metadata = readMetadata(path)
        val uri = path.toAbsolutePath().toUri()
        DriverManager.getConnection("jdbc:sqlite:$uri?mode=ro").use { connection ->
            val sql = when (metadata.schemaVersion) {
                PoiSchemaVersion.V3.metadataVersion ->
                    "SELECT i.lat, i.lon, d.data FROM poi_index i JOIN poi_data d ON d.id = i.id " +
                        "WHERE i.lat BETWEEN ? AND ? AND i.lon BETWEEN ? AND ? LIMIT ?"
                PoiSchemaVersion.V4.metadataVersion ->
                    "SELECT (i.minLat + i.maxLat) / 2.0, (i.minLon + i.maxLon) / 2.0, d.data " +
                        "FROM poi_index i JOIN poi_data d ON d.id = i.id " +
                        "WHERE i.maxLat >= ? AND i.minLat <= ? AND i.maxLon >= ? AND i.minLon <= ? LIMIT ?"
                else -> error("Mapsforge-POI-Schema v${metadata.schemaVersion} wird nicht unterstützt.")
            }
            connection.prepareStatement(sql).use { statement ->
                statement.setDouble(1, bounds.south)
                statement.setDouble(2, bounds.north)
                statement.setDouble(3, bounds.west)
                statement.setDouble(4, bounds.east)
                statement.setInt(5, limit)
                statement.executeQuery().use { result ->
                    val points = ArrayList<PreviewPoint>(metadata.poiCount.coerceAtMost(limit.toLong()).toInt())
                    val labels = ArrayList<PreviewLabel>()
                    while (result.next()) {
                        val latitude = result.getDouble(1)
                        val longitude = result.getDouble(2)
                        if (latitude !in -90.0..90.0 || longitude !in -180.0..180.0) continue
                        points += PreviewPoint(latitude, longitude, PreviewFeatureClass.POI)
                        if (labels.size < MAX_PREVIEW_LABELS) {
                            poiName(result.getString(3).orEmpty())?.let { name ->
                                labels += PreviewLabel(
                                    latitude = latitude,
                                    longitude = longitude,
                                    text = name.take(80),
                                    labelClass = PreviewLabelClass.POI,
                                    importance = 35,
                                )
                            }
                        }
                    }
                    return PreviewDataset(
                        bounds = bounds,
                        points = points,
                        polylines = emptyList(),
                        sourceNodeCount = metadata.poiCount,
                        sourceWayCount = 0,
                        labels = labels,
                    )
                }
            }
        }
    }

    private fun poiName(data: String): String? {
        val tags = data.split('\r', '\n', '\u0000', ';').mapNotNull { entry ->
            val separator = entry.indexOf('=')
            if (separator <= 0) null else entry.substring(0, separator) to entry.substring(separator + 1)
        }.toMap()
        return (tags["name:de"] ?: tags["name"] ?: tags["ref"])?.trim()?.takeIf(String::isNotBlank)
    }

    private fun Connection.metadataValue(name: String): String? =
        prepareStatement("SELECT value FROM metadata WHERE name = ?").use { statement ->
            statement.setString(1, name)
            statement.executeQuery().use { result -> if (result.next()) result.getString(1) else null }
        }

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

    private const val MAX_PREVIEW_LABELS = 8_000
}
