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

def highlight_diff(data, color='yellow'):
    attr = 'background-color: {}'.format(color)
    other = data.xs('cur', axis='columns', level=-1)
    return pd.DataFrame(np.where(data.ne(other, level=0), attr, ''),
                        index=data.index, columns=data.columns)


def main():
    engine = drw.create_db_connection('config.ini', 'gemeenten')

    vvb_cur = drw.read_from_db('vvb_validatie', engine)
    vvb_cur['Jaar'] = pd.to_datetime(vvb_cur['Jaar'])
    vvb_cur['id'] = vvb_cur['u_id'].astype(str) + '_' + vvb_cur['Jaar'].dt.year.astype(str)
    vvb_cur = vvb_cur.set_index('id')

    vvb_new = drw.read_from_db('vvb_calculated_inc_21', engine)
    vvb_new = vvb_new.rename({'ID Organisatie':'u_id', 'Peiljaar':'Jaar'}, axis=1)
    vvb_new['Jaar'] = pd.to_datetime(vvb_new['Jaar'])
    vvb_new['id'] = vvb_new['u_id'].astype(str) + '_' + vvb_new['Jaar'].dt.year.astype(str)
    vvb_new = vvb_new.set_index('id')


    df_check = pd.concat([vvb_cur, vvb_new], axis='columns', keys=['cur', 'new'])
    df_check = df_check.swaplevel(axis='columns')[vvb_cur.columns[1:]]
    #print(df_check)
    df_check.to_csv('checken.csv', sep=';', decimal=',')

    df_check = df_check[df_check['Jaar']['cur'] == '2019-01-01']
    df_cur = df_check.xs('cur', axis='columns', level=-1)
    df_new = df_check.xs('new', axis='columns', level=-1)
    df_diff = df_check.ne(df_new, level=0)
    #df_diff.to_csv('diff.csv', sep=';', decimal=',')

    #df_cur = df_cur.drop('Organisatienaam', 'Jaar')
    df_cur = df_cur.select_dtypes(exclude=['object', 'datetime64[ns]'])
    df_new = df_new.select_dtypes(exclude=['object', 'datetime64[ns]'])

    print(df_cur)
    print(df_new)
    df_subt = df_new.subtract(df_cur, axis=1)
    df_subt.to_csv('diff_subt.csv', sep=';', decimal=',')


if __name__ == '__main__':
    main()