from google.cloud import bigquery
from google.oauth2 import service_account
import plotly.graph_objects as go
import plotly.tools as tls

from plotly.offline import plot, iplot, init_notebook_mode
from IPython.core.display import display, HTML
init_notebook_mode(connected = True)
config={'showLink': False, 'displayModeBar': False}

credentials = service_account.Credentials.from_service_account_file(
    'cred.json')
project_id = 'intricate-yew-283611'
client = bigquery.Client(credentials= credentials,project=project_id)


query_job = client.query("""
SELECT  bt.*, ct.TotalNoTransactions as CashTransactions, lt.TotalNoTransactions as LiteTransactions,
btr.TotalBitcoins as TotalBitcoins, ctr.TotalBitcoins as TotalCash, ltr.TotalBitcoins as TotalLitecoins,
bad.distinct_addresses as BitcoinAddresses, cad.distinct_addresses as CashAddresses, lad.distinct_addresses as LitecoinAddresses
FROM `intricate-yew-283611.Blockchain.Bitcoin_Transactions_Lite` as bt
left join `intricate-yew-283611.Blockchain.Cash_Transactions_Lite` as ct on bt.Datum = ct.Datum
left join `intricate-yew-283611.Blockchain.Litecoin_Transactions_Lite` as lt on bt.Datum = lt.Datum
left join `intricate-yew-283611.Blockchain.Bitcoins_Traded` as btr on bt.Datum = btr.Datum
left join `intricate-yew-283611.Blockchain.Cash_Traded` as ctr on bt.Datum = ctr.Datum
left join `intricate-yew-283611.Blockchain.Litecoin_Traded` as ltr on bt.Datum = ltr.Datum
left join `intricate-yew-283611.Blockchain.Bitcoin_activeAd` as bad on bt.Day = bad.Day and bt.Month = bad.Month and bt.Year = bad.Year
left join `intricate-yew-283611.Blockchain.cash_activeAd` as cad on bt.Day = cad.Day and bt.Month = cad.Month and bt.Year = cad.Year
left join `intricate-yew-283611.Blockchain.Litecoin_activeAd` as lad on bt.Datum = cast(lad.Datum as string)
Order By Year, Month, Day
 
 """)
results = query_job.result()

results_df = results.to_dataframe()
print(results_df)



fig2 = go.FigureWidget()

fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['TotalNoTransactions'],name = 'Bitcoin',line = dict(color = 'Light Blue')))
fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['CashTransactions'], name = 'Bitcoin Cash',line = dict(color = 'rgb(237, 97, 97)')))
fig2.add_trace(go.Scatter(x = results_df['Datum'], y = results_df['LiteTransactions'], name = 'Litecoin', line = dict(color = 'Light Green')))
 
names = ["Bitcoin", "Bitcoin Cash", "Litecoin"]
lst = ['TotalBitcoins','TotalCash','TotalLitecoins']
colors = ['Light Blue','rgb(237, 97, 97)','Light Green']
for num in range(0,3):
    a = results_df[lst[num]].tolist()
    b = []
    for x in a:
        if (x is not None):
            b.append(float(x))
        else:
            b.append(x)
    fig2.add_trace(go.Scatter(x = results_df['Datum'], y = b, name = names[num], line = dict(color =colors[num] ), visible = False))


fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['BitcoinAddresses'],name = 'Bitcoin',line = dict(color = 'Light Blue')))
fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['CashAddresses'], name = 'Bitcoin Cash',line = dict(color = 'rgb(237, 97, 97)')))
fig2.add_trace(go.Scatter(visible = False,x = results_df['Datum'], y = results_df['LitecoinAddresses'], name = 'Litecoin', line = dict(color = 'Light Green')))
 

fig2.update_layout(paper_bgcolor = 'White',xaxis_title="Date",
    yaxis_title='Number of Transactions',plot_bgcolor="#FFFFFF",hovermode="x",
    hoverdistance=100, 
    spikedistance=1000,
     updatemenus=[
                    dict(
                        active=0,
                        buttons=list([
                       
                            dict(label="Number of Transactions",
                             method="update",
                             args=[{"visible": 
                            [True,True,True,False, False,False, False, False, False]},
                               {"title": "",
                                'yaxis': {'title': 'Number of Transactions', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                  ]),
                            
                        dict(label=" Coins Traded",
                             method="update",
                             args=[{"visible": 
                                  [ False, False, False, True,True,True,False,False,False]},
                                  {"title": "",
                                   'yaxis': {'title': 'Number of Coins Traded', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                   
                                ]),
                            
                            dict(label=" Active Addresses",
                             method="update",
                             args=[{"visible": 
                                  [ False, False, False,False,False,False,True,True,True]},
                                  {"title": "",
                                   'yaxis': {'title': 'Number of Active Addresses', 'ticks' : 'outside', 'showline': True, 'linecolor': 'black' }}
                                   
                                ]),
                        
               
            ]),
                        x=0.2,
                        y = 1.3
                        
        )
]
)
fig2.update_xaxes(showspikes=True,spikethickness=2,
        spikedash="dot",
        spikecolor="#999999",
        spikemode="across",showgrid=False,ticks = 'outside', showline=True, linecolor='black',rangeslider_visible=True,
                rangeselector=dict(
        buttons=list([
            dict(count=1, label="1m", step="month", stepmode="backward"),
            dict(count=6, label="6m", step="month", stepmode="backward"),
            dict(count=1, label="YTD", step="year", stepmode="todate"),
            dict(count=1, label="1y", step="year", stepmode="backward"),
            dict(step="all")
        ]), x=0.65,
            y = 1.2
    ))
fig2.update_yaxes(autorange = True, fixedrange= False,showgrid=False, ticks = 'outside', showline=True, linecolor='black')



plot(fig2, filename = 'fig2.html', config = config)
#ThebeLab
display(HTML('fig2.html'))