import pandas as pd
import numpy as np

id_header = ['Ebene' + str(i) for i in range(1, 11)]
values2_header = ['VJ', 'AJ', 'FC', 'Plan_ori', 'Plan_Prozent', 'Stk', 'VAK', 'BE_Prozent', 'Plan_VJ', 'Plan_Stk_VJ', 'Plan',
                  'Jan', 'Feb', 'Mar', 'Apr', 'Mai', 'Jun', 'Jul', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez', 'Periode13']
season_header = ['Jan', 'Feb', 'Mar', 'Apr', 'Mai', 'Jun', 'Jul', 'Aug', 'Sep', 'Okt', 'Nov', 'Dez']
header = ['text', 'costcenter', 'department'] + id_header + values2_header


def expand(df, header, values_label):
    for i, key in enumerate(header):
        df[key] = df[values_label].str[i]
    return df


def apply_season(df):
    df['Saison'] = df['Ebene1'].str.contains('Umsatzerlöse|Materialaufwand|Verkaufsabh. Kosten')
    for i, key in enumerate(season_header):
        df[key] = np.where(df['Saison'], df['Plan'] * df[key + '_2'] / 100, df['Plan'] * 8.3333 / 100)
    return df


def data_cleansing(filename):
    df = pd.read_json(filename)
    df['values2'] = df['values2'].apply(lambda v: list(v.items()))
    df = df.explode('values2')
    df['department'], df['values2'] = zip(*df['values2'])

    df['id'] = df['id'].str.split(';')
    df = expand(df, id_header, 'id')
    df = expand(df, values2_header, 'values2')
    return df


df = data_cleansing('Planung/V1.json')
season = df[(df['level'] == 2) & (df['Ebene1'] == 'Umsatzerlöse')]
plan = df[df['accounts'].apply(lambda a: len(a) > 0)]

plan = pd.merge(plan, season, how='left', on=['Ebene2', 'department'], suffixes=('', '_2'))
plan = apply_season(plan)

# plan = plan[header]
plan.to_csv(open('Planung/V1.csv', 'w', newline=''), sep=';', decimal=',', encoding='ansi', index=False)