import data_read_write as drw
import pandas as pd
import numpy as np

def main():
    config_path = 'config.ini'
    excel_path = 'Data_RBB_def.xlsx'
    excel_sheets = ['2019', '2020']
    
    engine_rbb = drw.create_db_connection(config_path, 'rbb')
    df_rbb_1920 = drw.read_excel_file(excel_path, excel_sheets)

    # 2021 uses commas as decimal separator
    # when writing this directly to the db, columns turn out as strings instead of double
    # therefore, write/read to csv has been added as workaround
    df_rbb_21 = drw.read_from_db('data_pbi', engine_rbb)

    # remove dashes and other NAs
    df_rbb_21 = df_rbb_21.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'^nvh$':np.nan}, regex=True)

    # remove weird symbols (e.g. at Nuffic)
    df_rbb_21 = df_rbb_21.replace(to_replace={r'€ ':''}, regex=True)

    # remove value for i2021.162986 (ICT Run/Change) for Belastingdienst
    df_rbb_21.at[df_rbb_21[df_rbb_21['Organisatienaam'] == 'Belastingdienst'].index[0], 'i2021.162986'] = np.nan

    # change u_id for Belastingdienst for 2021
    df_rbb_21.at[df_rbb_21[df_rbb_21['Organisatienaam'] == 'Belastingdienst'].index[0], 'u_id'] = 99999

    
    df_rbb_21.to_csv('Data_RBB_21.csv', index=False, sep=';', decimal=',')
    df_rbb_21 = pd.read_csv('Data_RBB_21.csv', sep=';', decimal=',')

    df_rbb_21['Jaar'] = '2021'

    df_rbb_21 = drw.remove_icode(df_rbb_21, 2021)

    columns_to_divide = [
        '162986',
        '163000'
    ]
    df_rbb_21[columns_to_divide] = df_rbb_21[columns_to_divide]/100

    # remove 0 values for 162999 (Totale personele overheadkosten plus uitbesteding) 
    # and 163000 (Totale personele overheadkosten (incl. uitbesteding) / totale apparaatskosten)
    df_rbb_21[['162999', '163000']] = df_rbb_21[['162999', '163000']].replace(to_replace={0:np.nan}, regex=False)

    # add 0 values for 162987-162996 (excl. 162992) and 162998 (to fix average calculations in PowerBI),
    # but only where 162987 is filled
    applicable_rows = df_rbb_21[df_rbb_21['162987'].notna()].index.tolist()
    overhead_cols = [
        'Organisatienaam',
        '162987',
        '162988',
        '162989',
        '162990',
        '162991',
        '162993',
        '162994',
        '162995',
        '162996',
        '162998'
    ]

    df_rbb_21.loc[applicable_rows, overhead_cols] = df_rbb_21.loc[applicable_rows, overhead_cols].fillna(0) 

    df_rbb_1921 = pd.concat([df_rbb_1920, df_rbb_21])

    drw.write_to_db(engine_rbb, 'rbb_validatie', df_rbb_1921, append=False)
    drw.write_to_db(engine_rbb, 'rbb', df_rbb_1920, append=False)


    #2023 update met data van 2022
    
    #maak een connectie met de nieuwe database
    engine_rbb_2023 = drw.create_db_connection(config_path, 'rbb_2023')
    engine_rbb_2023.connect()
    df_rbb_22 = drw.read_from_db('data_pbi', engine_rbb_2023)

    # remove dashes and other NAs
    df_rbb_22 = df_rbb_22.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'^nvh$':np.nan}, regex=True)

    # remove weird symbols (e.g. at Nuffic)
    df_rbb_22 = df_rbb_22.replace(to_replace={r'€ ':''}, regex=True)

     # change u_id for Belastingdienst for 2022
    df_rbb_22.at[df_rbb_22[df_rbb_22['Organisatienaam'] == 'Belastingdienst'].index[0], 'u_id'] = 99999

    #voeg nul aan kolomen toe
    columns_to_replace_null = ['i2022.' + str(number) for number in [162987, 162988, 162989, 162990, 162991, 162993, 162994, 162995, 162996, 162998]]

    #lege strings veranderen in None
    df_rbb_22[columns_to_replace_null] = df_rbb_22[columns_to_replace_null].replace('', np.nan)

    #voeg nul aan kolomen toe
    df_rbb_22['i2022.162999'] = pd.to_numeric(df_rbb_22['i2022.162999'], errors='coerce')
    if (df_rbb_22['i2022.162999'] > 0).any():
        df_rbb_22.loc[df_rbb_22['i2022.162999'] > 0, columns_to_replace_null] = df_rbb_22.loc[df_rbb_22['i2022.162999'] > 0, columns_to_replace_null].fillna(0)

    df_rbb_22.to_csv('Data_RBB_22.csv', index=False, sep=';', decimal=',')
    df_rbb_22 = pd.read_csv('Data_RBB_22.csv', sep=';', decimal=',')

    df_rbb_22['Jaar'] = '2022'

    df_rbb_22 = drw.remove_icode(df_rbb_22, 2022)

    columns_to_divide = [
        '162986',
        '163000'
    ]
    df_rbb_22[columns_to_divide] = df_rbb_22[columns_to_divide]/100

    df_rbb_1922 = pd.concat([df_rbb_1921, df_rbb_22])
    df_rbb_1922

    drw.write_to_db(engine_rbb, 'rbb_validatie_2023', df_rbb_1922, append=False)


     #2024 update met data van 2023
    
    #maak een connectie met de nieuwe database
    engine_rbb_2024 = drw.create_db_connection(config_path, 'rbb_2024')
    engine_rbb_2024.connect()
    df_rbb_23 = drw.read_from_db('data_pbi', engine_rbb_2024)

    # remove dashes and other NAs
    df_rbb_23 = df_rbb_23.replace(to_replace={r'^-$':np.nan, r'^nvt$':np.nan, r'^NB$':np.nan, r'^nvh$':np.nan}, regex=True)

    # remove weird symbols (e.g. at Nuffic)
    df_rbb_23 = df_rbb_23.replace(to_replace={r'€ ':''}, regex=True)

     # change u_id for Belastingdienst for 2022
    df_rbb_23.at[df_rbb_23[df_rbb_23['Organisatienaam'] == 'Belastingdienst'].index[0], 'u_id'] = 99999

    #voeg nul aan kolomen toe
    columns_to_replace_null = ['i2023.' + str(number) for number in [162987, 162988, 162989, 162990, 162991, 162993, 162994, 162995, 162996, 162998]]

    #lege strings veranderen in None
    df_rbb_23[columns_to_replace_null] = df_rbb_23[columns_to_replace_null].replace('', np.nan)

    # #voeg nul aan kolomen toe
    # df_rbb_23['i2023.162999'] = pd.to_numeric(df_rbb_23['i2023.162999'], errors='coerce')
    # if (df_rbb_23['i2023.162999'] > 0).any():
    #     df_rbb_23.loc[df_rbb_22['i2023.162999'] > 0, columns_to_replace_null] = df_rbb_23.loc[df_rbb_23['i2023.162999'] > 0, columns_to_replace_null].fillna(0)

    df_rbb_23.to_csv('Data_RBB_23.csv', index=False, sep=';', decimal=',')
    df_rbb_23 = pd.read_csv('Data_RBB_23.csv', sep=';', decimal=',')

    df_rbb_23['Jaar'] = '2023'

    df_rbb_23 = drw.remove_icode(df_rbb_23, 2023)

    columns_to_divide = [
        '162986',
        '163000'
    ]
    df_rbb_23[columns_to_divide] = df_rbb_23[columns_to_divide]/100

    df_rbb_1923 = pd.concat([df_rbb_1922, df_rbb_23])
    df_rbb_1923

    drw.write_to_db(engine_rbb, 'rbb_validatie_2024', df_rbb_1923, append=False)




if __name__ == "__main__":
    main()