import sqlite3
import gpxpy
import gpxpy.gpx
import sys

# Source: https://github.com/libracore/erpnextswiss/blob/master/erpnextswiss/erpnextswiss/swisstopo.py
def LV95ToWGSLatitude(east, north):
    # Convert the projection coordinates E (easting) and N (northing) in MN95
    # into the civilian system (Bern = 0 / 0) and express in the unit 1000 km.
    y_aux = (east - 2600000) / 1000000
    x_aux = (north - 1200000) / 1000000

    # Process latitude calculation.
    lat = (
        (16.9023892 + (3.238272 * x_aux))
        + -(0.270978 * pow(y_aux, 2))
        + -(0.002528 * pow(x_aux, 2))
        + -(0.0447 * pow(y_aux, 2) * x_aux)
        + -(0.0140 * pow(x_aux, 3))
    )

    # Unit 10000" to 1" and converts seconds to degrees notation.
    lat = lat * 100 / 36
    return lat


# Source: https://github.com/libracore/erpnextswiss/blob/master/erpnextswiss/erpnextswiss/swisstopo.py
def LV95ToWGSLongitude(east, north):
    # Convert the projection coordinates E (easting) and N (northing) in MN95
    # into the civilian system (Bern = 0 / 0) and express in the unit 1000 km.
    y_aux = (east - 2600000) / 1000000
    x_aux = (north - 1200000) / 1000000

    # Process longitude calculation.
    lng = (
        (2.6779094 + (4.728982 * y_aux))
        + +(0.791484 * y_aux * x_aux)
        + +(0.1306 * y_aux * pow(x_aux, 2))
        + -(0.0436 * pow(y_aux, 3))
    )

    # Unit 10000" to 1" and converts seconds to degrees notation.
    lng = lng * 100 / 36
    return lng


db = sqlite3.connect("hydrants_live.sqlite")

gpx = gpxpy.gpx.GPX()
gpx.name = "Hydranten"
gpx.description = "Hydranten in Urdorf"

cur = db.cursor()
cur.execute(
    f"SELECT easting, northing, abgaenge, installationsjahr, strasse, hydrantennummer FROM hydrants WHERE plz = {sys.argv[1]} AND hydrantennummer != 659"
)
rows = cur.fetchall()
for row in rows:
    easting = row[0]
    northing = row[1]
    abgaenge = row[2]
    installationsjahr = row[3]
    strasse = row[4]
    hydrantennummer = row[5]
    gpx_wps = gpxpy.gpx.GPXWaypoint()
    gpx_wps.longitude = LV95ToWGSLongitude(row[0], row[1])
    gpx_wps.latitude = LV95ToWGSLatitude(row[0], row[1])
    gpx_wps.name = f"Hydrant #: {hydrantennummer}"
    gpx_wps.description = f"""
    name=Hydrant #: {hydrantennummer}
    description=Abgänge: {abgaenge}
    Installationsjahr: {installationsjahr}
    Strasse: {strasse}
    """
    gpx_wps.symbol = "Hydrant"
    gpx.waypoints.append(gpx_wps)
abgaenge

print(gpx.to_xml())