import pandas as pd
import os
import sys
import datetime
utils_path = os.path.join(os.path.abspath(os.getenv('PROCESSING_DIR')),'utils')
if utils_path not in sys.path:
    sys.path.append(utils_path)
import util_files
import util_cloud
import util_carto
import logging
from zipfile import ZipFile
import tabula

# Set up logging
# Get the top-level logger object
logger = logging.getLogger()
for handler in logger.handlers: logger.removeHandler(handler)
logger.setLevel(logging.INFO)
# make it print to the console.
console = logging.StreamHandler()
logger.addHandler(console)
logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

# name of table on Carto where you want to upload data
# this should be a table name that is not currently in use
dataset_name = 'soc_067_rw1_climate_risk_index' #check

logger.info('Executing script for dataset: ' + dataset_name)
# create a new sub-directory within your specified dir called 'data'
# within this directory, create files to store raw and processed data
data_dir = util_files.prep_dirs(dataset_name)

'''
Download data and save to your data directory
'''
# insert the url used to download the pdf from the source website
url = 'https://germanwatch.org/sites/germanwatch.org/files/20-2-01e%20Global%20Climate%20Risk%20Index%202020_14.pdf'

# download the pdf from the url, scrape the tables that store the data,
# and combine them into a single pandas dataframe
df = pd.concat(tabula.read_pdf(url, pages=list(range(37, 41)), multiple_tables=True, lattice=True)[:-1])

# export the pandas dataframe to a csv file
raw_data_file = os.path.join(data_dir, 'data.csv')
df.to_csv(raw_data_file, index = False)

'''
Process data
'''
# rename the columns to get rid of spaces and special characters 
# and convert them all to lowercase letters
df.rename(columns = {'CRI\rRank': 'cri_rank',
                     'Country': 'country',
                     'CRI\rscore': 'cri_score',
                     'Fatalities\rin 2018\r(Rank)': 'fatalities_2018_rank',
                     'Fatalities per\r100 000 inhab-\ritants (Rank)': 'fatalities_per_100k',
                     'Losses in mil-\rlion US$ (PPP)\r(Rank)': 'losses_usdm_ppp_rank',
                     'Losses per\runit  GDP in\r% (Rank)': 'losses_per_gdp__rank'},
          inplace = True)

# create a new column 'datetime' to store the time period of the data
# as the first date of the year 
df['datetime'] = datetime.datetime(2018, 1, 1)

# save processed dataset to csv
processed_data_file = os.path.join(data_dir, dataset_name+'_edit.csv')
df.to_csv(processed_data_file, index=False)

'''
Upload processed data to Carto
'''
logger.info('Uploading processed data to Carto.')
util_carto.upload_to_carto(processed_data_file, 'LINK')

'''
Upload original data and processed data to Amazon S3 storage
'''
# initialize AWS variables
aws_bucket = 'wri-public-data'
s3_prefix = 'resourcewatch/'

logger.info('Uploading original data to S3.')
# Upload raw data file to S3

# Copy the raw data into a zipped file to upload to S3
raw_data_dir = os.path.join(data_dir, dataset_name+'.zip')
with ZipFile(raw_data_dir,'w') as zip:
    zip.write(raw_data_file, os.path.basename(raw_data_file))
# Upload raw data file to S3
uploaded = util_cloud.aws_upload(raw_data_dir, aws_bucket, s3_prefix+os.path.basename(raw_data_dir))

logger.info('Uploading processed data to S3.')
# Copy the processed data into a zipped file to upload to S3
processed_data_dir = os.path.join(data_dir, dataset_name+'_edit.zip')
with ZipFile(processed_data_dir,'w') as zip:
    zip.write(processed_data_file, os.path.basename(processed_data_file))
# Upload processed data file to S3
uploaded = util_cloud.aws_upload(processed_data_dir, aws_bucket, s3_prefix+os.path.basename(processed_data_dir))
import pandas as pd
import os
import sys
import datetime
utils_path = os.path.join(os.path.abspath(os.getenv('PROCESSING_DIR')),'utils')
if utils_path not in sys.path:
    sys.path.append(utils_path)
import util_files
import util_cloud
import util_carto
import logging
from zipfile import ZipFile
import tabula

# Set up logging
# Get the top-level logger object
logger = logging.getLogger()
for handler in logger.handlers: logger.removeHandler(handler)
logger.setLevel(logging.INFO)
# make it print to the console.
console = logging.StreamHandler()
logger.addHandler(console)
logging.basicConfig(format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

# name of table on Carto where you want to upload data
# this should be a table name that is not currently in use
dataset_name = 'soc_067_rw1_climate_risk_index' #check

logger.info('Executing script for dataset: ' + dataset_name)
# create a new sub-directory within your specified dir called 'data'
# within this directory, create files to store raw and processed data
data_dir = util_files.prep_dirs(dataset_name)

'''
Download data and save to your data directory
'''
# insert the url used to download the pdf from the source website
url = 'https://germanwatch.org/sites/germanwatch.org/files/20-2-01e%20Global%20Climate%20Risk%20Index%202020_14.pdf'

# download the pdf from the url, scrape the tables that store the data,
# and combine them into a single pandas dataframe
df = pd.concat(tabula.read_pdf(url, pages=list(range(37, 41)), multiple_tables=True, lattice=True)[:-1])

# export the pandas dataframe to a csv file
raw_data_file = os.path.join(data_dir, 'data.csv')
df.to_csv(raw_data_file, index = False)

'''
Process data
'''
# rename the columns to get rid of spaces and special characters 
# and convert them all to lowercase letters
df.rename(columns = {'CRI\rRank': 'cri_rank',
                     'Country': 'country',
                     'CRI\rscore': 'cri_score',
                     'Fatalities\rin 2018\r(Rank)': 'fatalities_2018_rank',
                     'Fatalities per\r100 000 inhab-\ritants (Rank)': 'fatalities_per_100k',
                     'Losses in mil-\rlion US$ (PPP)\r(Rank)': 'losses_usdm_ppp_rank',
                     'Losses per\runit  GDP in\r% (Rank)': 'losses_per_gdp__rank'},
          inplace = True)

# create a new column 'datetime' to store the time period of the data
# as the first date of the year 
df['datetime'] = datetime.datetime(2018, 1, 1)

# save processed dataset to csv
processed_data_file = os.path.join(data_dir, dataset_name+'_edit.csv')
df.to_csv(processed_data_file, index=False)

'''
Upload processed data to Carto
'''
logger.info('Uploading processed data to Carto.')
util_carto.upload_to_carto(processed_data_file, 'LINK')

'''
Upload original data and processed data to Amazon S3 storage
'''
# initialize AWS variables
aws_bucket = 'wri-public-data'
s3_prefix = 'resourcewatch/'

logger.info('Uploading original data to S3.')
# Upload raw data file to S3

# Copy the raw data into a zipped file to upload to S3
raw_data_dir = os.path.join(data_dir, dataset_name+'.zip')
with ZipFile(raw_data_dir,'w') as zip:
    zip.write(raw_data_file, os.path.basename(raw_data_file))
# Upload raw data file to S3
uploaded = util_cloud.aws_upload(raw_data_dir, aws_bucket, s3_prefix+os.path.basename(raw_data_dir))

logger.info('Uploading processed data to S3.')
# Copy the processed data into a zipped file to upload to S3
processed_data_dir = os.path.join(data_dir, dataset_name+'_edit.zip')
with ZipFile(processed_data_dir,'w') as zip:
    zip.write(processed_data_file, os.path.basename(processed_data_file))
# Upload processed data file to S3
uploaded = util_cloud.aws_upload(processed_data_dir, aws_bucket, s3_prefix+os.path.basename(processed_data_dir))
# Set up logging # Get the top-level logger object # make it print to the console. # name of table on Carto where you want to upload data # this should be a table name that is not currently in use #check # create a new sub-directory within your specified dir called 'data' # within this directory, create files to store raw and processed data Download data and save to your data directory # insert the url used to download the pdf from the source website # download the pdf from the url, scrape the tables that store the data, # and combine them into a single pandas dataframe # export the pandas dataframe to a csv file Process data # rename the columns to get rid of spaces and special characters # and convert them all to lowercase letters # create a new column 'datetime' to store the time period of the data # as the first date of the year # save processed dataset to csv Upload processed data to Carto Upload original data and processed data to Amazon S3 storage # initialize AWS variables # Upload raw data file to S3 # Copy the raw data into a zipped file to upload to S3 # Upload raw data file to S3 # Copy the processed data into a zipped file to upload to S3 # Upload processed data file to S3