import requests
import mysql.connector
from mysql.connector import Error

def get_coordinates(postal_code):
    url = f"https://nominatim.openstreetmap.org/search?format=json&postalcode={postal_code}&country=Germany&limit=1"
    response = requests.get(url)
    data = response.json()
    
    if data:
        return data[0]['lat'], data[0]['lon']
    else:
        return None, None

def update_database():
    try:
        connection = mysql.connector.connect(
            host='ep1.btn.de',
            user='btn',
            password='ERcCrf1FzDttIn81t0Ga',
            database='btn_vaillantticket'
        )
        
        cursor = connection.cursor()
        cursor.execute("SELECT ID, PLZ FROM tb_unternehmen WHERE latitude IS NULL OR longitude IS NULL")
        
        companies = cursor.fetchall()
        
        for company_id, postal_code in companies:
            latitude, longitude = get_coordinates(postal_code)
            
            if latitude and longitude:
                update_query = "UPDATE tb_unternehmen SET latitude = %s, longitude = %s WHERE ID = %s"
                cursor.execute(update_query, (latitude, longitude, company_id))
                connection.commit()
                
                print(f"Updated ID {company_id} with coordinates: {latitude}, {longitude}")
            else:
                print(f"No coordinates found for postal code {postal_code}")
        
    except Error as e:
        print(f"Error: {e}")
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

if __name__ == "__main__":
    update_database()
