# Import needed libraries
import sqlalchemy
from sqlalchemy import create_engine, types
from sqlalchemy.exc import SQLAlchemyError
import pandas as pd
import numpy as np


# Maak verbinding met de kurtosis_db
def create_connection_kur():
    engine = create_engine(
        'mysql+pymysql://qvanderlinden:XEMFj9Y8ErKyyY6df2MK@dev.kurtosis.nl/venster_voor_rbb'
    )
    return engine


# Maak verbinding met de webhosting_db
def create_connection_webh():
    engine = create_engine(
        'mysql+pymysql://deb129704n2_powerbi-connector:s%40G5QCEMx4U%26AC%246TYoh@s184.webhostingserver.nl/deb129704n2_prd-rbb-dashboard'
    )
    return engine


# importeer RBB_vragenlijst_old
def import_df(table_name, engine):
    # Loading data from the whole RBB_vragenlijst_old into a Pandas DataFrame
    query = f"SELECT * FROM {table_name}"
    df = pd.read_sql(query, engine)
    return df

def vergelijker(df1,df2):
    # Stap 1: Voeg een kolom toe om de bron te markeren
    df1['Source'] = 'df1'
    df2['Source'] = 'df2'

    # Stap 2: Converteer de relevante kolommen naar dezelfde naam voor consistente concat
    df1_renamed = df1.rename(columns={'Jaar': 'Col1', '13302': 'Col2','13307':'Col3'})
    df2_renamed = df2.rename(columns={'Jaar': 'Col1', '13302': 'Col2','13307':'Col3'})

    # Stap 3: Concateneer de DataFrames
    combined_df = pd.concat([df1_renamed, df2_renamed], ignore_index=True, sort=False)

    # Stap 4: Groepeer op de kolommen en tel het voorkomen
    duplicates = combined_df.groupby(['Col1', 'Col2','Col3']).size().reset_index(name='count')

    # Stap 5: Identificeer unieke rijen (count == 1)
    unique_rows = duplicates[duplicates['count'] == 1]

    # Stap 6: Merge terug met de gecombineerde DataFrame om de bron te achterhalen
    unique_df = unique_rows.merge(combined_df, on=['Col1', 'Col2','Col3'], how='left')

    # Stap 7: Split de unieke rijen per bron
    unique_to_df1 = unique_df[unique_df['Source'] == 'df1'][['Col1', 'Col2','Col3']]
    unique_to_df2 = unique_df[unique_df['Source'] == 'df2'][['Col1', 'Col2','Col3']]

    # Stap 3: Print de resultaten
    print(f"Unieke combinaties in df1 {len(unique_to_df1)}:")
    for combo in unique_to_df1:
        print(f"df1: {combo}")

    print(f"Unieke combinaties in df2 {len(unique_to_df1)}:")
    for combo in unique_to_df2:
        print(f"df2: {combo}")


# Hoofdfunctie voor de uitvoering
def main():
    table_kurtosis = 'RBB_vragenlijst_compleet'
    table_webhosting = 'rbb_validatie_2024'

    engine_kur = create_connection_kur()
    engine_webh = create_connection_webh()

    try:
        df_kur = import_df(table_kurtosis, engine_kur)
        df_webh = import_df(table_webhosting, engine_webh)
        vergelijker(df_kur,df_webh)

    
    finally:
        engine_kur.dispose()
        engine_webh.dispose()

if __name__ == '__main__':
    main()