#! /bin/python3

import os
import os.path
import sys
import argparse
from Functions import DatabaseManager as dbman
from yahooquery import Ticker
import pandas as pd
import csv
import requests
import requests_cache
from ratelimiter import RateLimiter

# Set up requests_cache to cache API responses
requests_cache.install_cache('yahoo_api_cache', expire_after=3600)

# Set up requests_ratelimiter to rate-limit API responses
rate_limiter = RateLimiter(max_calls=1, period=5)
session = requests_cache.CachedSession()
session.headers.update({'User-Agent': 'Mozilla/5.0'})

db = dbman("StocksDB1")

webEn = sys.argv[1]

if webEn == "Y":
	# Read Shares list from Notepad
	myfile = open("/home/nick/Documents/Shares")
	fields = myfile.readlines()
	for fds in fields:
		sfds = fds.split(', ')
		Stocks = [word.strip('\n') for word in sfds]

	# Get data in the current column for each stock's valuation table
	dow_currPrice = {}
	dow_sector = {}
	dow_industry = {}
	dow_priceBook = {}
	dow_priceEquity = {}
	dow_peg = {}
	dow_priceSales = {}
	dow_debtEquity = {}
	dow_growth = {}
	dow_freeCashFlow = {}
	dow_totalDebt = {}
	dow_totalCash = {}
	dow_marketCap = {}
	dow_enterVal = {}
	for ticker in Stocks:
		# Import Data
		temp_T = Ticker(ticker, session=session)
		with rate_limiter:
			## Financial Data
			tempFinData = temp_T.financial_data
			## Index Trend
			tempIndexTrend = temp_T.index_trend
			## Key Stats
			tempKeyStats = temp_T.key_stats
			## Summary Detail
			tempSummDet = temp_T.summary_detail
			## Summary Profile
			tempSummProf = temp_T.summary_profile
			## Cashflow
			temp_cashFlow = temp_T.cash_flow(trailing=False)
		
			# Process Data
			## Financial Data - Current Price, DE Ratio, Total Cash, Total Debt
			temp_finData = tempFinData[ticker]
			temp_currPrice = temp_finData['currentPrice']
			### temp_debtEquity = temp_finData['debtToEquity']
			temp_totalCash = temp_finData['totalCash']
			temp_totalDebt = temp_finData['totalDebt']
			## Index Trend - 5yr Growth
			temp_indexTrend = tempIndexTrend[ticker]
			temp_estIndTrnd = temp_indexTrend['estimates']
			temp_5YrGrowth = temp_estIndTrnd[4]
			temp_growth = temp_5YrGrowth['growth']
			## Key Stats - PB Ratio, PEG Ratio, Enterprise Value
			temp_keyStats = tempKeyStats[ticker]
			temp_priceBook = temp_keyStats['priceToBook']
			### temp_peg = temp_keyStats['pegRatio']
			temp_enterVal = temp_keyStats['enterpriseValue']
			## Summary Detail - PE Ratio, PS Ratio, Market Cap
			temp_summDet = tempSummDet[ticker]
			### temp_priceEquity = temp_summDet['trailingPE']
			temp_priceSales = temp_summDet['priceToSalesTrailing12Months']
			temp_marketCap = temp_summDet['marketCap']
			## Summary Profile - Sector, Industry
			temp_summProf = tempSummProf[ticker]
			temp_sector = temp_summProf['sector']
			temp_industry = temp_summProf['industry']
			## Cashflow - Free Cashflow
			temp_CF_Sh = temp_cashFlow.shape
			temp_CF_LR = temp_CF_Sh[0] - 1
			temp_freeCashflow = temp_cashFlow['FreeCashFlow'][temp_CF_LR]
			
			# Add Data to Dictionary
			dow_currPrice[ticker] = temp_currPrice
			dow_sector[ticker] = temp_sector
			dow_industry[ticker] = temp_industry
			dow_priceBook[ticker] = temp_priceBook
			### dow_priceEquity[ticker] = temp_priceEquity
			### dow_peg[ticker] = temp_peg
			dow_priceSales[ticker] = temp_priceSales
			### dow_debtEquity[ticker] = temp_debtEquity
			dow_growth[ticker] = temp_growth
			dow_freeCashFlow[ticker] = temp_freeCashflow
			dow_totalDebt[ticker] = temp_totalDebt
			dow_totalCash[ticker] = temp_totalCash
			dow_marketCap[ticker] = temp_marketCap
			dow_enterVal[ticker] = temp_enterVal
	
	# Convert Data from Dictionary to Dataframe
	df_currPrice = pd.DataFrame(dow_currPrice.items(), columns=['Stock','Price'])
	df_sector = pd.DataFrame(dow_sector.items(), columns=['Stock','Sector'])
	df_industry = pd.DataFrame(dow_industry.items(), columns=['Stock','Industry'])
	df_priceBook = pd.DataFrame(dow_priceBook.items(), columns=['Stock','PB'])
	#df_priceEquity = pd.DataFrame(dow_priceEquity.items(), columns=['Stock','PE'])
	#df_peg = pd.DataFrame(dow_peg.items(), columns=['Stock','PEG'])
	df_priceSales = pd.DataFrame(dow_priceSales.items(), columns=['Stock','PS'])
	#df_debtEquity = pd.DataFrame(dow_debtEquity.items(), columns=['Stock','DE'])
	df_growth = pd.DataFrame(dow_growth.items(), columns=['Stock','Growth'])
	df_freeCashFlow = pd.DataFrame(dow_freeCashFlow.items(), columns=['Stock','FCF'])
	df_totalDebt = pd.DataFrame(dow_totalDebt.items(), columns=['Stock','Debt'])
	df_totalCash = pd.DataFrame(dow_totalCash.items(), columns=['Stock','Cash'])
	df_marketCap = pd.DataFrame(dow_marketCap.items(), columns=['Stock','Market Cap'])
	df_enterVal = pd.DataFrame(dow_enterVal.items(), columns=['Stock','Enterprise Value'])
	
	# To Add df_priceEquity, df_peg & df_debtEquity
	df_stockInfo = pd.concat([df_currPrice, df_totalCash, df_totalDebt, df_growth, df_priceBook, df_enterVal, df_priceSales, df_marketCap, df_freeCashFlow], axis = 1)
	df_stockInfo = df_stockInfo.T.drop_duplicates().T
		
	# Connect to Database
	conn = db.con()
	
	# Remove Existing Tables
	exStr = "DROP TABLE IF EXISTS 'Full Stock Info'"
	db.ex(exStr)
	
	# Add Dataframes to Database
	df_stockInfo.to_sql('Full Stock Info', conn) 
else:
	# Connect to Database
	conn = db.con()
	
	# Pull Dataframes from Database
	df_stockInfo = pd.read_sql("SELECT * FROM 'Full Stock Info'", con=conn)
	
'''
length = len(combined_stats[0])
with open("/home/nick/Documents/test.csv", 'w', newline='') as fp:
	a = csv.writer(fp, delimiter=',')
	for y in range(length):
		a.writerow([x[y] for x in combined_stats])
'''