from bokeh.plotting import Figure, output_file, show, output_server
from bokeh.models.widgets import Slider, Select, TextInput, MultiSelect
from bokeh.models import ColumnDataSource, HoverTool, TapTool, Text, Circle, SingleIntervalTicker 
from bokeh.models import Button, DataRange1d, Range, Range1d
from bokeh.models.layouts import VBox
from bokeh.models.glyphs import Text
from bokeh.io import vform, curdoc, vplot, hplot, output_file, show
from bokeh.embed import file_html, autoload_static, components
from bokeh.resources import CDN, INLINE
from bokeh.core.properties import Instance
from bokeh.palettes import Spectral6

import csv
import pandas as pd
import sqlite3
import numpy as np
import matplotlib.pyplot as plt
import os

#os.chdir('C:\\Users\Lars Johannes\Dropbox\Python\TrainingScripts\WB Project')


#load data from database and convert it to table where each indicator has an own column
conn = sqlite3.connect('WBdata.sqlite')

data =  pd.read_sql('select ' 
                    'data.Country, '
                    'data.Indicator, '
                    'Indicator.Ind_Code, '
                    'data.Year, ' 
                    'data.Value, ' 
                    'Country.Color, '
                    'Country.Region, '
                    'Country.Income '
                    'from data join '
                            'Country on data.Country = Country.Name join ' 
                            'Indicator on data.Indicator = Indicator.Name', conn)
axis_map = dict()

i=0
while i < len(data):
    axis_map[data.Indicator[i]] = data.Ind_Code[i]
    i = i+1
    
ranges = []

for i in list(set(data.Indicator)):
    df = data.loc[data['Indicator'] == i]
    ranges.append({'Indicator':str(i),'Max': max(df.Value), 'Min': min(df.Value)})
    
ranges = pd.DataFrame(ranges)
ranges = ranges.set_index('Indicator')

#build data frame with unique list pfindicator name and max/min per indicator

data = pd.pivot_table(data, 
                      values='Value',
                      columns = 'Ind_Code', 
                      index = ['Year', 
                               'Country',
                               'Color', 
                               'Region', 
                               'Income',  
                               ]
                    )

data.reset_index(level=['Year', 'Country', 'Region', 'Income', 'Color'], inplace=True)

regions = data.Region.unique()
regions = regions.tolist()
#data.to_csv('test.csv')

start = min(data.Year)
end = max(data.Year)

x_axis = Select(title='X-Axis', options=sorted(axis_map.keys()), value = 'GDP per capita (current US$)')
y_axis = Select(title='Y-Axis', options=sorted(axis_map.keys()), value = 'Life expectancy at birth, total (years)')
#color = Select(title='Color', options=sorted['Income', 'Region'], value = 'Income')
start_yr = Slider(title = 'Start Year', start=start, end=end, value=start, step=1)
end_yr = Slider(title = 'End Year', start=start, end=end-1, value=end-1, step=1)
#region = MultiSelect(title='Color', options=sorted(regions), value = regions)
years = Slider(title = 'Year', start = start_yr.value, end = end_yr.value, value = (end_yr.value), step=1)

#cerate source data frame to be populated contextually
source = ColumnDataSource(data=dict(x=[],y=[], year=[], country=[], color=[], alpha=[], region=[]))

#set up hover tool with relevant data points


hover = HoverTool(tooltips =[
    ("Country", "@country"),
    ("Year", "@year"),
    ("Region", "@region"),
    (x_axis.value, "@x{0.00}"),
    (y_axis.value, "@y{0.00}")
])

#Part3: start with defining output file to start design of graph

#define parameters for output graph "p"
p = Figure(plot_height=600, plot_width=700, title="")
p.circle(x="x", y="y", source=source, size="alpha", color="color", line_color=None, fill_alpha=1)
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None
p.add_tools(TapTool(), hover)


#p.add_glyph(Text(x=1.1, y=18, text=str('Year'), text_font_size = '72pt', text_color='#eeeeee'))

#Filter indicators to be displayed
def select_indicator():
    selected = data[
        (data.Year >= start_yr.value) &
        (data.Year <= end_yr.value) &
        (data.Year == years.value)]
    
    return selected
select_indicator()


#define function update
def update():
#create df as data frame of selected indicators
    df = select_indicator()
    x_name = axis_map[x_axis.value]
    y_name = axis_map[y_axis.value]

    p.xaxis.axis_label = x_axis.value
    p.yaxis.axis_label = y_axis.value
    
    p.x_range = Range1d(start = float(ranges.loc[x_axis.value,"Min"]), end = float(ranges.loc[x_axis.value,"Max"]))
    p.y_range = Range1d(start = float(ranges.loc[y_axis.value,"Min"]), end = float(ranges.loc[y_axis.value,"Max"]))
    
    source.data = dict(
        x=df[x_name],
        y=df[y_name],
        year=df["Year"],
        country=df["Country"],
        color=df["Color"],
        region=df["Region"],
        alpha=(df["Year"]-float(start))/(float(end)-float(start))*10    
        )

def animate_update():
    year = float(years.value) + 1
    if year > float(end)-1: year = start_yr.value
    years.value = year
    update()

def animate():
    if button.label == 'Play':
        button.label = 'Pause'
        curdoc().add_periodic_callback(animate_update, 200)
    else:
        button.label = 'Play'
        curdoc().remove_periodic_callback(animate_update)

def slider_update (attrname, old, new):
    year = years.value
    
    
years.on_change('value', slider_update)


button = Button(label='Play')
button.on_click(animate)

#def axis_update():
#    p.x_range = Range1d(start = float(ranges.loc[x_axis.value,"Min"]), end = float(ranges.loc[x_axis.value,"Max"]))
#    p.y_range = Range1d(start = float(ranges.loc[y_axis.value,"Min"]), end = float(ranges.loc[y_axis.value,"Max"]))

#x_axis.on_change('x_axis.value', lambda attr, old, new: axis_update())
#y_axis.on_change('y_axis.value', lambda attr, old, new: axis_update())

controls = [x_axis, y_axis, start_yr, end_yr, years]
for control in controls:
    control.on_change('value', lambda attr, old, new: update())


update() # initial load of the data


widgets = VBox(x_axis, y_axis, start_yr, end_yr, years, button)

layout = hplot(widgets, p)

curdoc().add_root(layout)
curdoc().title = "WB data viz"
#output_file('db.html')