import requests
import mysql.connector
from mysql.connector import Error

def insert_gas_price_data():
    connection = None

    try:
        url = "https://enever.nl/api/gasprijs_vandaag.php?token=5a7d9b371fe147cfc8100bcf6d9ebd55"
        response = requests.get(url)

        if response.status_code != 200:
            raise Exception('Fout bij het ophalen van de gasdata.')

        data = response.json()

        if data is None or 'data' not in data:
            raise Exception('Fout bij het decoderen van de JSON-data.')

        connection = mysql.connector.connect(
            host='127.0.0.1',
            database='alfen',
            user='alfen_user',
            password='5uVgr%f%s2P5GR@3q!',
            port=3307
        )

        if connection.is_connected():
            cursor = connection.cursor()

            query = """
            INSERT INTO gas_price_data (datetime, provider_code, price) 
            VALUES (%s, %s, %s) 
            ON DUPLICATE KEY UPDATE price = %s
            """

            providers = {
                'EGSI': 'prijsEGSI',
                'EOD': 'prijsEOD',
                'ANWB': 'prijsANWB',
                'BE': 'prijsBE',
                'EE': 'prijsEE',
                'EN': 'prijsEN',
                'EVO': 'prijsEVO',
                'EZ': 'prijsEZ',
                'FR': 'prijsFR',
                'GSL': 'prijsGSL',
                'MDE': 'prijsMDE',
                'NE': 'prijsNE',
                'VDB': 'prijsVDB',
                'VON': 'prijsVON',
                'WE': 'prijsWE',
                'ZP': 'prijsZP'
            }

            for entry in data['data']:
                datetime = entry['datum']
                for code, price_field in providers.items():
                    if price_field in entry:
                        price = entry[price_field]
                        try:
                            cursor.execute(query, (datetime, code, price, price))
                            connection.commit()
                        except Error as e:
                            print(f"Fout bij invoeren van gasdata voor provider {code}: {e}")
                            continue

    except Error as e:
        print(f"Fout bij databaseverbinding: {e}")
    except Exception as e:
        print(f"Algemene fout: {e}")
    finally:
        if connection is not None and connection.is_connected():
            cursor.close()
            connection.close()

if __name__ == "__main__":
    insert_gas_price_data()