import pandas as pd
import psycopg2 as ps


def checkNan(text, default, add_single_quotes=False):
	if text == 'nan':
		return str(default)

	if add_single_quotes:
		return str('\'' + text + '\'')

	return str(text)


data = pd.read_csv('forecast.csv')
delete_query = 'DELETE FROM forecast_corp;'
insert_query = 'INSERT INTO forecast_corp (id, date, country, reseller_type, corp_id, corp_name, corp_size_categorization, gmv_forecast, forecast_update_created_on) VALUES \r\n'

for index, row in data.iterrows():
	id = str(row['id'])
	date = checkNan(str(row['date']), 'NULL')
	country = checkNan(str(row['country']), 'NULL')
	reseller_type = checkNan(str(row['reseller_type']), 'NULL')
	corp_id = checkNan(str(row['corp_id']), '0.0')
	corp_name = checkNan(str(row['corp_name']), 'NULL')
	corp_size_categorization = checkNan(str(row['corp_size_categorization']), 'NULL')
	gmv_forecast = str(row['gmv_forecast'])
	forecast_update_created_on = checkNan(str(row['forecast_update_created_on']), 'NULL', True)

	insert_query += '\t(' + id + ', \'' + date + '\', \'' + country + '\', \'' + reseller_type + '\', ' + corp_id + ', \'' \
					+ corp_name + '\', \'' + corp_size_categorization + '\', ' + gmv_forecast + ', ' + forecast_update_created_on \
					+ '),\r\n'

insert_query = insert_query[0:len(insert_query) - 3].replace('\'NULL\'', 'NULL') + ';'

with open('query.txt', 'w') as f:
	f.write(insert_query)

connection = None
try:
	connection = ps.connect(user='biuser',
								  password='IguanaBi01',
								  host='bi.clp7yqehsl0w.us-east-1.rds.amazonaws.com',
								  port='5432',
								  database='bi')
	delete_cursor = connection.cursor()
	delete_cursor.execute(delete_query)

	insert_cursor = connection.cursor()
	insert_cursor.execute(insert_query)
	connection.commit()

	result = insert_cursor.rowcount

	if result is not None and result > 0:
		print('Yeah! It worked.')
	else:
		print('You suck!')

	insert_cursor.close()
except (Exception, ps.Error) as error:
	print('Error while fetching data from PostgreSQL', error)
finally:
	# closing database connection.
	if connection:
		connection.close()