import pandas as pd
import os
import numpy as np
from zipfile import BadZipfile

os.chdir(r'../Data/Data_website/Scrapping')

##Word-Category mapping
wordlistMap = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='full lists')
wordlistMap['id'] = wordlistMap.index+1
wordlistMap = wordlistMap.melt(id_vars='id')
wordlistMap = wordlistMap.rename(columns={'variable':'var_name','value':'word'})
wordlistMap['var_name'] = wordlistMap['var_name'] + wordlistMap['id'].astype(str)
wordlistMap = wordlistMap.drop(columns=['id'])
wordlistMap = wordlistMap.dropna(axis=0, subset=['word']).reset_index(drop=True)

##ESG Category - Dictionary mapping
dictmapESG = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='esg dictionaries')
dictmapESG = dictmapESG[['dict_esg1','dict_e1','dict_s1','dict_g1', 'dict_esg2','dict_e2','dict_s2','dict_g2']]
dictmapESG = dictmapESG.melt()
dictmapESG = dictmapESG.rename(columns={'variable':'dict','value':'word'})
dictmapESG = pd.merge(dictmapESG, wordlistMap, how='inner', on='word')
dictmapESG.drop(columns='word', inplace=True)
dictmapESG = dictmapESG[~dictmapESG.var_name.str.contains('non_esg')]
dictmapESG = dictmapESG.sort_values(by=['dict','var_name'])

##Non-ESG Category - Dictionary mapping
dictmapNonESG = pd.read_excel(r'../Glossaries/Dictionaries_final.xlsx', sheet_name='individual lists')
dictmapNonESG.drop(columns=['esg_lower','esg_upper'], inplace=True)
dictmapNonESG = dictmapNonESG.melt()
dictmapNonESG['variable'] = dictmapNonESG['variable'].str.removesuffix('_lower')
dictmapNonESG['variable'] = dictmapNonESG['variable'].str.removesuffix('_upper')
dictmapNonESG = dictmapNonESG.rename(columns={'variable':'dict','value':'word'})
dictmapNonESG = dictmapNonESG.dropna(axis=0, subset=['word']).reset_index(drop=True)
dictmapNonESG = pd.merge(dictmapNonESG, wordlistMap, how='inner', on='word')
dictmapNonESG.drop(columns='word', inplace=True)
dictmapNonESG = dictmapNonESG[dictmapNonESG.var_name.str.contains('non_esg')]
dictmapNonESG = dictmapNonESG.sort_values(by=['dict','var_name'])

##Function for constructing the measures 
def measureConstruction(df):
    try:
        df = df.dropna(subset=['word_count']).reset_index(drop=True)
    except KeyError:
        return None
    splits = df['esg_lower'].str.split(',', expand=True)
    splits.columns = splits.columns+1
    df = pd.concat([df,splits.add_prefix('esg_lower')], axis=1)
    splits = df['esg_upper'].str.split(',', expand=True)
    splits.columns = splits.columns+1
    df = pd.concat([df,splits.add_prefix('esg_upper')], axis=1)
    splits = df['non_esg_lower'].str.split(',', expand=True)
    splits.columns = splits.columns+1
    df = pd.concat([df,splits.add_prefix('non_esg_lower')], axis=1)
    splits = df['non_esg_upper'].str.split(',', expand=True)
    splits.columns = splits.columns+1
    df = pd.concat([df,splits.add_prefix('non_esg_upper')], axis=1)
    df.drop(columns=['esg_lower','esg_upper','non_esg_lower','non_esg_upper'], inplace=True)
    
    for i in dictmapESG.dict.unique():
        df[i]=0
        for j in dictmapESG[dictmapESG.dict==i].var_name.unique():
            df[i] = df[i] + df[j].astype(int)
    
    for i in dictmapNonESG.dict.unique():
        df[i]=0
        for j in dictmapNonESG[dictmapNonESG.dict==i].var_name.unique():
            df[i] = df[i] + df[j].astype(int)
    
    df = df[['fm_id', 'url_id', 'url_datetime', 'word_count', 'dict_esg1','dict_e1','dict_s1','dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'complex', 'constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']]
    return df

##Measure Construction
#PE Firms
for file in os.listdir('./ESGcounts/PE Firms'):
    print(file)
    try: 
        df = pd.read_pickle('./ESGcounts/PE Firms/%s' %file, compression='zip')
    except BadZipfile:
        os.remove('./ESGcounts/PE Firms/%s' %file) 
        continue                    
    df = measureConstruction(df)
    if df is None:
        os.remove('./ESGcounts/PE Firms/%s' %file) 
        continue
    else:
        df_source = pd.read_pickle('./snapshotURLlevelList/PE Firms/%s' %file, compression='zip')
        df_source = pd.merge(df_source, df, how='outer', on=['fm_id','url_id','url_datetime'])
        df_source.rename(columns={'complex':'Complex', 'constrain':'Constrain'}, inplace=True)
        df_source.drop(columns=['urlkey', 'url'], inplace=True)
        
        df_source['word_count'] = pd.to_numeric(df_source['word_count'])
        df_source = df_source.groupby(['fm_id', 'snapshot_year', 'snapshot_date', 'snapshot_datetime'])[['length', 'word_count', 'dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']].sum()
        df_source = df_source.apply(lambda x: x/df_source.word_count*10000 if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
        for colName in ['dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2']:
            df_source['%s_ihs' %colName] = np.arcsinh(df_source[['%s' %colName]])
        df_source = df_source.apply(lambda x: np.log(1+x) if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
        df_source['word_count'] = df_source['word_count']/10000
        df_source['logWordCount'] = np.log(1+df_source['word_count'])
        df_source['logLength'] = np.log(df_source['length']/1000000)
        df_source.to_pickle('./ESGmetrics/PE Firms/%s' %file, compression='zip')
            
#HF Firms
for file in os.listdir('./ESGcounts/HF Firms'):
    print(file)
    try: 
        df = pd.read_pickle('./ESGcounts/HF Firms/%s' %file, compression='zip')
    except BadZipfile:
        os.remove('./ESGcounts/HF Firms/%s' %file) 
        continue                    
    df = measureConstruction(df)
    if df is None:
        os.remove('./ESGcounts/HF Firms/%s' %file) 
        continue
    else:
        df_source = pd.read_pickle('./snapshotURLlevelList/HF Firms/%s' %file, compression='zip')
        df_source = pd.merge(df_source, df, how='outer', on=['fm_id','url_id','url_datetime'])
        df_source.rename(columns={'complex':'Complex', 'constrain':'Constrain'}, inplace=True)
        df_source.drop(columns=['urlkey', 'url'], inplace=True)
        
        df_source['word_count'] = pd.to_numeric(df_source['word_count'])
        df_source = df_source.groupby(['fm_id', 'snapshot_year', 'snapshot_date', 'snapshot_datetime'])[['length', 'word_count', 'dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal']].sum()
        df_source = df_source.apply(lambda x: x/df_source.word_count*10000 if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
        for colName in ['dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2']:
            df_source['%s_ihs' %colName] = np.arcsinh(df_source[['%s' %colName]])
        df_source = df_source.apply(lambda x: np.log(1+x) if x.name in ['dict_esg1', 'dict_e1', 'dict_s1', 'dict_g1', 'dict_e2', 'dict_s2', 'dict_g2', 'dict_esg2', 'Complex', 'Constrain', 'legal', 'litigious', 'negative', 'positive', 'strong_modal', 'tax', 'uncertain', 'valuation', 'weak_modal'] else x)
        df_source['word_count'] = df_source['word_count']/10000
        df_source['logWordCount'] = np.log(1+df_source['word_count'])
        df_source['logLength'] = np.log(df_source['length']/1000000)
        df_source.to_pickle('./ESGmetrics/HF Firms/%s' %file, compression='zip')