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

def main():
    config_path = 'config.ini'
    excel_path = 'Data_VvW_def.xlsx'
    excel_sheets = ['2015', '2017', '2019']
    
    engine_vvw = drw.create_db_connection(config_path, 'waterschappen')
    df_vvw_1520 = 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_vvw_21 = drw.read_from_db('data_pbi', engine_vvw)
    df_vvw_21.to_csv('Data_VvW_21.csv', index=False, sep=';', decimal=',')
    df_vvw_21 = pd.read_csv('Data_VvW_21.csv', sep=';', decimal=',')
    df_vvw_21['Jaar'] = '2021-01-01'
    df_vvw_21['Jaar'] = pd.to_datetime(df_vvw_21['Jaar'])
    df_vvw_21 = drw.remove_icode(df_vvw_21, 2021)
    
    df_vvw_1521 = pd.concat([df_vvw_1520, df_vvw_21])

    drw.write_to_db(engine_vvw, 'vvw_validatie', df_vvw_1521, append=False)
    drw.write_to_db(engine_vvw, 'vvw', df_vvw_1520, append=False)


    #nieuwe data van 2023 ingevuld in 2024
    
    #maak een connectie met de nieuwe database
    engine_vvw_2024 = drw.create_db_connection(config_path, 'waterschappen_2024')
    engine_vvw_2024.connect()
    df_vvw_23 = drw.read_from_db('data_pbi', engine_vvw_2024)

    # remove dashes and other NAs
    df_vvw_23 = df_vvw_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_vvw_23 = df_vvw_23.replace(to_replace={r'€ ':''}, regex=True)

  
    #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_vvw_23[columns_to_replace_null] = df_vvw_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_vvw_23.to_csv('Data_vvw_23.csv', index=False, sep=';', decimal=',')
    df_vvw_23 = pd.read_csv('Data_vvw_23.csv', sep=';', decimal=',')

    df_vvw_23['Jaar'] = '2023-01-01 00:00:00'

    df_vvw_23 = drw.remove_icode(df_vvw_23, 2023)

    # columns_to_divide = [
    #     '162986',
    #     '163000'
    # ]
    # df_vvw_23[columns_to_divide] = df_vvw_23[columns_to_divide]/100

    df_rbb_1923 = pd.concat([df_vvw_1521, df_vvw_23])
    df_rbb_1923

    drw.write_to_db(engine_vvw, 'vvw_validatie_2024', df_rbb_1923, append=False)

if __name__ == "__main__":
    main()