import pandas as pd 
from dateutil.parser import parse
import datetime


df = pd.read_csv("leden.csv")


df['GEBOORTEDATUM'] = df['GEBOORTEDATUM'].apply(lambda x: parse(x, dayfirst=True).strftime("%d-%m-%Y") if not "?" in x else x)
df['LAND'] = df["POSTCODE + PLAATS"].apply(lambda x: "België" if "België" in x else "Nederland")
# df['POSTCODE'] = df[df["LAND"] == "Nederland"]["POSTCODE + PLAATS"].apply(lambda x: x)#f"{x.split(' ', maxsplit=2)[0]} {x.split(' ', maxsplit=2)[1]}") 
# df['PLAATS'] = df[df["LAND"] == "Nederland"]["POSTCODE + PLAATS"].apply(lambda x: x.split(" ", maxsplit=2)[2]) 
df['POSTCODE'] = df["POSTCODE + PLAATS"].apply(lambda x: x.split(',')[0].split(' ')[0] if "," in x else f"{x.split(' ', maxsplit=2)[0]} {x.split(' ', maxsplit=2)[1]}")
df['PLAATS'] = df["POSTCODE + PLAATS"].apply(lambda x: x.split(',')[0].split(' ')[1] if "," in x else x.split(" ", maxsplit=2)[2])

df['OPLEIDING'] = df['STUDIE'].apply(lambda x: "Japanstudies" if x[0] == "J" else "Overig")
df['OVERIGE_OPLEIDING'] = df['STUDIE'].apply(lambda x: "" if x[0] == "J" else x.replace("J", "").replace("K", "Koreastudies"))
df['TOESTEMMING_CONTRIBUTIE'] = int(1)
df['TOESTEMMING_NIEUWSBRIEF'] = int(1)

df['TELEFOON'] = df['TELEFOON'].apply(lambda x: x.replace(" ", "").replace("-", ""))
df['TELEFOON'] = df['TELEFOON'].apply(lambda x: f"0{x}" if x and x[0] == "6" else x)

df['LID GEWORDEN'] = df['LID GEWORDEN'].apply(lambda x: x.replace("-", "/"))

df.to_excel("leden_nieuw.xlsx", index=False)

df_web = pd.DataFrame()

df_web['user_login'] = df[['VOORNAAM', 'TUSSENVOEGSEL', 'ACHTERNAAM']].apply(lambda x: '.'.join(x[x.notnull()]).lower(), axis = 1)
df_web['user_email'] = df['E-MAIL']
df_web['first_name'] = df['VOORNAAM']
df_web['last_name'] = df[['TUSSENVOEGSEL', 'ACHTERNAAM']].apply(lambda x: ' '.join(x[x.notnull()]), axis = 1)
df_web['display_name'] = df_web['first_name'] + " " + df_web['last_name']
df_web['billing_first_name'] = df_web['first_name']
df_web['billing_last_name'] = df_web['last_name']
df_web['billing_address_1'] = df['ADRES']
df_web['billing_city'] = df['PLAATS']
df_web['billing_postcode'] = df['POSTCODE']
df_web['billing_country'] = df['LAND'].apply(lambda x: "NL" if x == "Nederland" else "BE")
df_web['billing_phone'] = df['TELEFOON']
df_web['billing_e-mail'] = df['E-MAIL']
df_web['geboortedatum'] = df['GEBOORTEDATUM']
df_web['opleiding'] = df['OPLEIDING']
df_web['opleiding_overige'] = df['OVERIGE_OPLEIDING']
df_web['iban'] = df['REKENINGNUMMER']
df_web['jaar_van_aanmelding'] = df['LID GEWORDEN']
df_web['toestemming_contributie'] = df['TOESTEMMING_CONTRIBUTIE']
df_web['toestemming_nieuwsbrief'] = df['TOESTEMMING_NIEUWSBRIEF']

print(df_web)
df_web.to_csv("leden_web.csv", index=False)

df_web.loc[df_web['last_name'].isin(["Wacanno", "Schaapsmeerders", "Ederer", "Kuilman"])].to_csv("ft.csv", index=False)
df_web.loc[df_web['last_name'].isin(["Flikweert", "Overzet", "Ogawa", "Lefering", "Trimbach"])].to_csv("et.csv", index=False)