import pandas as pd
import numpy as np
import os
import data_read_write as drw
from datetime import datetime

exports_path = "C:\\Users\\MarkvanKruistum\\Kurtosis\\Sharepoint - Vensters\\Vensters voor Gemeenten\\Data\\export"

def parse_export_answers(year):
    year_path = os.path.join(exports_path, 'VvB{}_data export.xlsx'.format(year))
    df = pd.read_excel(year_path)

    # Split Excel export into 2 dataframes
    # Fix column headers of first part
    if year in [2017, 2018, 2019, 2020]:
        df_answers_pt1 = df.iloc[12:, :3]
        df_answers_pt1.columns = df_answers_pt1.iloc[0]
        df_answers_pt1 = df_answers_pt1.drop(df_answers_pt1.index[0])
        df_answers_pt1.columns.name = None
        df_answers_pt1 = df_answers_pt1[df_answers_pt1['Waardetype'] == 'Key Waarde']
        df_answers_pt1 = df_answers_pt1.drop('Waardetype', axis=1)

        df_answers_pt2 = df.iloc[13:, 4:]

        df = pd.merge(df_answers_pt1, df_answers_pt2, left_index=True, right_index=True)
        df = df.reset_index(drop=True)
        
    icode_dict = {2017: '921', 2018: '1055', 2019: '1222', 2020: '1388', 2021:'2020'}
    df = drw.remove_icode(df, icode_dict[year])
    
    return df


def main():
    years = [2017, 2018, 2019, 2020, 2021]
    icode_dict = {2017: '921', 2018: '1055', 2019: '1222', 2020: '1388', 2021:'2020'}


    df_list = []
    # Parse export of every year
    for year in years:    
        df = parse_export_answers(year)
        df['Peiljaar'] = datetime.strptime('1-1-{}'.format(year - 1), '%d-%m-%Y')
        df_list.append(df)
    # Combine yearly exports into one large dataframe
    df = pd.concat(df_list)
    df = df[df['17234'].isin([0, 3, 'Gemeente', 'Overig'])]


    # List the columns that are calculated in the portal
    # These columns are not saved in the database table, 
    # but rather calculated and added in a later stage
    # Therefore, these get dropped in this stage
    calculated_columns_set = set()
    for year in years:
        path_calculated = 'C:\\Users\\MarkvanKruistum\\Kurtosis\\Sharepoint - Vensters\\Vensters voor Gemeenten\\Data\\export\\questions\\calculated_columns_{}.txt'.format(year)
        list_calc = open(path_calculated, 'r').read().replace('i{}.'.format(icode_dict[year]), '').split('\n')
        calculated_columns_set.update(list_calc)
        
    df = df.drop(calculated_columns_set, axis=1)


    df[['103095','103096','103097','103098','35952','35950','35951','35953','35955','35956','35957','35954','35958','35962','35964','35965','35966']] = df[['103095','103096','103097','103098','35952','35950','35951','35953','35955','35956','35957','35954','35958','35962','35964','35965','35966']].replace({'Volledig of grotendeels in eigen beheer' : 0, 'Ongeveer evenveel zelf als uitbesteed':0.5, 'Volledig of grotendeels uitbesteed':1})


    # convert compatible columns to numeric
    df = df.apply(pd.to_numeric, errors='ignore')
    # Except peiljaar
    df['Peiljaar'] = pd.to_datetime(df['Peiljaar'])

    # drop empty columns
    df = df.dropna(axis=1, how='all')

    df['35698'] = np.nan
    df['35700'] = np.nan
    df['97941'] = np.nan

    engine = drw.create_db_connection('config.ini', 'gemeenten')
    drw.write_to_db(engine, 'vvb_uncalculated', df, append=False)

if __name__ == '__main__':
    main()