import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from pylab import rcParams
from statsmodels.tsa.stattools import adfuller
from statsmodels.tsa.seasonal import seasonal_decompose
from statsmodels.tsa.arima.model import ARIMA
from pmdarima.arima import auto_arima
from sklearn.metrics import mean_squared_error
import math
import re
from datetime import date
import nsepy 
import warnings
warnings.filterwarnings("ignore")
####################################          Live data extraction              ###################################################
##Extracting data from nsepy package
da=date.today()
gold= pd.DataFrame(nsepy.get_history(symbol="SGBAUG24",series="GB", start=date(2016,9,1), end=da))
bond= pd.DataFrame(nsepy.get_history(symbol="IRFC",series="N2", start=date(2012,1,1), end=da))

#############################                 Live data  extraction end                  ###############################################

# Heatmap - to check collinearity
def heatmap(x):
    plt.figure(figsize=(16,16))
    sns.heatmap(x.corr(),annot=True,cmap='Blues',linewidths=0.2) #data.corr()-->correlation matrix
    fig=plt.gcf()
    fig.set_size_inches(10,8)
    plt.show()
heatmap(gold)
heatmap(bond)
###############################                Live data to Feature engineering            ################################################3             

##Taking close price as our univariate variable
##For gold
gold=pd.DataFrame(gold["Close"])
gold["date"]=gold.index
gold["date"]=gold['date'].astype(str)
gold[["year", "month", "day"]] = gold["date"].str.split(pat="-", expand=True)
gold['Dates'] = gold['month'].str.cat(gold['year'], sep ="-")
gold.Dates=pd.to_datetime(gold.Dates)
gold.set_index('Dates',inplace=True)
col_sgb=pd.DataFrame(gold.groupby(gold.index).Close.mean())

##For bond
bond=pd.DataFrame(bond["Close"])
bond["date"]=bond.index
bond["date"]=bond['date'].astype(str)
bond[["year", "month", "day"]] = bond["date"].str.split(pat="-", expand=True)
bond['Dates'] = bond['month'].str.cat(bond['year'], sep ="-")
bond.Dates=pd.to_datetime(bond.Dates)
bond.set_index('Dates',inplace=True)
col_bond=pd.DataFrame(bond.groupby(bond.index).Close.mean())

col_sgb.columns = ["Avg_price"]
col_bond.columns = ["Avg_price"]

col_bond.isnull().sum()
col_sgb.isnull().sum()

############################                  SQL connection with monthly data           ################################################ 
###############################                SQL database is created                  ################################################3             

# Connect to the database
from sqlalchemy import create_engine
engine_sgb = create_engine('sqlite:///gold_database.db', echo=False)
col_sgb.to_sql('SGB', con=engine_sgb,if_exists='replace')
df_sgb = pd.read_sql('select * from SGB',engine_sgb )

df_sgb.Dates=pd.to_datetime(df_sgb.Dates)
df_sgb.set_index('Dates',inplace=True)


engine_irfcb = create_engine('sqlite:///irfcb_database.db', echo=False)
col_bond.to_sql('IRFCB', con=engine_irfcb,if_exists='replace')
df_bond = pd.read_sql('select * from IRFCB',engine_irfcb)

df_bond.Dates=pd.to_datetime(df_bond.Dates)
df_bond.set_index('Dates',inplace=True)
###############################                SQL data to python                 ################################################3             



# Plotting
def plotting_bond(y):
    fig, ax = plt.subplots(figsize=(20, 6))
    ax.plot(y,marker='.', linestyle='-', linewidth=0.5, label='Monthly Average')
    ax.plot(y.resample('Y').mean(),marker='o', markersize=8, linestyle='-', label='Yearly Mean Resample')
    ax.set_ylabel('Avg_price')
    ax.legend();
plotting_bond(df_sgb)
plotting_bond(df_bond)

#univariate analysis of Average Price
df_sgb.hist(bins = 50)
df_bond.hist(bins = 50)

# check Stationary and adf test
def test_stationarity(timeseries):
    #Determing rolling statistics
    rolmean = timeseries.rolling(12).mean()
    rolstd = timeseries.rolling(12).std()
    #Plot rolling statistics:
    fig, ax = plt.subplots(figsize=(16, 4))
    ax.plot(timeseries, label = "Original Price")
    ax.plot(rolmean, label='rolling mean');
    ax.plot(rolstd, label='rolling std');
    plt.legend(loc='best')
    plt.title('Rolling Mean and Standard Deviation - Removed Trend and Seasonality')
    plt.show(block=False)
    
    print("Results of dickey fuller test")
    adft = adfuller(timeseries,autolag='AIC')
    print('Test statistic = {:.3f}'.format(adft[0]))
    print('P-value = {:.3f}'.format(adft[1]))
    print('Critical values :')
    for k, v in adft[4].items():
        print('\t{}: {} - The data is {} stationary with {}% confidence'.format(k, v, 'not' if v
    
      y:
        a = print("The retrun of SGB is {a} and the return of IRFC Bond is {b} after {c} months".format(a=x,b=y,c=t))
    else:
        a = print("The return of IRFC Bond is{a} and the return of SGB Bond is {b} after {c} months".format(a=x,b=y,c=t))
    return a
output_(gain_sgb,gain_bond, n)