import csv
import pandas as pd
import os
import glob
import numpy as np

os.getcwd()
os.chdir(r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV')


###combine all novel wells
path = r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\playTS\noisekey'             
all_files = glob.glob(os.path.join(path, "*"))

df_novelkey = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
df_novelkey['dbscan1k'] = 1
df_novelkey.rename(columns={"Uploadkey_noise": "UploadKey"}, inplace=True)
df_novelkey_keep = df_novelkey[['UploadKey','dbscan1k']]


###combine full well clusters (without iteration)
path = r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\playTS\cluster_1k'             
all_files = glob.glob(os.path.join(path, "*"))

df_wellclusters = pd.concat([pd.read_csv(fp).assign(cluster=os.path.basename(fp).split('.')[0]) for fp in all_files])
df_wellclusters_keep = df_wellclusters[['UploadKey','cluster','date','year','month','Play']]
df_wellclusters_keep.drop_duplicates(inplace=True)

df_full = df_wellclusters_keep.merge(df_novelkey_keep, on="UploadKey", how="left")
df_full['dbscan1k'] = df_full['dbscan1k'].replace(np.nan, 0)


###merge with state mandatory disclosure dates and keep wells drilled after the mandatory reporting dates
df_raw = pd.read_csv(r'C:\Users\anson\Dropbox\Dissertation\Fracfocus\FracFocusCSV\Fracfocus_raw\merged_raw_original.csv')
df_state = df_raw[['UploadKey','StateNumber','Latitude','Longitude']]
df_state.drop_duplicates(inplace=True)
df_full_state = df_full.merge(df_state, on="UploadKey", how="left")

df_mandate = pd.read_csv("state_man.csv")
df_mandate['man_date'] = pd.to_datetime(df_mandate[['year', 'month', 'day']])
df_mandate_keep = df_mandate[['StateNumber','man_date']]
df_full_state_man = df_full_state.merge(df_mandate_keep, on="StateNumber", how="left")
df_full_state_man['date']= pd.to_datetime(df_full_state_man['date'])
df_man = df_full_state_man[df_full_state_man['date'] > df_full_state_man['man_date']]


###merge with TS variables and control variables
df_TS = pd.read_csv("TS_final.csv")
df_control = pd.read_csv("controls.csv")
df_control_keep = df_control[['UploadKey','OperatorName','ln_OGprod_byplay','ln_OGprod_byplay_y','log_wellcount_byplay','log_cumsum_byplay','opexp_byplay']]
df_man_TS = df_man.merge(df_TS, on="UploadKey", how="left")
df_man_TS_c = df_man_TS.merge(df_control_keep, on='UploadKey', how="left")


###generate innovative operator variables (operator-shale play level)
df_man_TS_c['inn_play'] = df_man_TS_c.groupby(['OperatorName','Play'])['dbscan1k'].transform('max')

#identify noninnovative wells that changed chemical clusters to prior disclosed ones
df_novelcluster = df_man_TS_c[df_man_TS_c['dbscan1k']==1]
inn_cluster = df_novelcluster['cluster'].to_list()
df_man_TS_c['imi'] = np.where((df_man_TS_c['inn_play']==0) & (df_man_TS_c['cluster'].isin(inn_cluster)), 1, 0)

df_man_TS_c.dropna(inplace=True)


###cross-sectional variables

#disct_strict
df_disc = pd.read_csv('disc_strict.csv')
df_man_TS_c1 = df_man_TS_c.merge(df_disc, on='StateNumber', how='left')

#budget_high
#split by state-year median (24192.67)
df_budget = pd.read_csv('budget_staff_gdp.csv')
df_man_TS_c2 = df_man_TS_c1.merge(df_budget, on=['StateNumber','year'], how='left')
df_man_TS_c2['spend_oggdp'] = df_man_TS_c2['spend']/df_man_TS_c2['oggdp']
df_man_TS_c2['budget_high'] = np.where(df_man_TS_c2['spend_oggdp']>=24192.67, 1, 0)

#urban
#import the 2019 US nation TIGER/Line Shapefile to QGIS
#import well data to QGIS
#intersect the well points and the MMSA polygons
#save the joined layer as csv
df_urban = pd.read_csv('urban_joined.csv')
df_urban['urban'] = np.where(df_urban['NAME'].isnull(), 0, 1)
df_urban_keep = df_urban[['UploadKey','urban']]
df_man_TS_c3 = df_man_TS_c2.merge(df_urban_keep, on='UploadKey', how='left')

#tort_post
df_tort = pd.read_csv('tort_filedate.csv')
df_tort['tort_filedate'] = pd.to_datetime(df_tort[['year', 'month', 'day']])
df_tort_keep = df_tort[['OperatorName','StateNumber','tort_filedate']]
df_man_TS_c4_temp = df_man_TS_c3.merge(df_tort_keep, on=['OperatorName','StateNumber'], how='left')
df_man_TS_c4_tort = df_man_TS_c4_temp.loc[~df_man_TS_c4_temp.tort_filedate.isnull(), :]
df_man_TS_c4_tort['tort_post'] = np.where(df_man_TS_c4_tort['tort_filedate']<=df_man_TS_c4_tort['date'], 1, 0)
df_man_TS_c4_tort_keep = df_man_TS_c4_tort[['UploadKey', 'tort_post']]
df_man_TS_c4 = df_man_TS_c3.merge(df_man_TS_c4_tort_keep, on='UploadKey', how='left')
df_man_TS_c4['tort_post'].fillna(0, inplace = True)


###operators with available stock price
df_pubop = pd.read_csv('operator_match.csv')
df_merge = df_man_TS_c4.merge(df_pubop, on='OperatorName', how='left')
df_merge['public'] = np.where(df_merge['ticker'].isnull(), 0, 1)

df_merge.to_csv('fracfocus_nomonitor.csv', index=False)

