from os.path import dirname, join
import sys
import numpy as np
import pandas.io.sql as psql

import psycopg2
from datetime import datetime
from bokeh.plotting import figure
from bokeh.layouts import layout, widgetbox
from bokeh.models import ColumnDataSource, HoverTool, Div
from bokeh.models.widgets import Slider, Select, TextInput, DatePicker, DateRangeSlider
from bokeh.io import curdoc

try:

    #Create a database session
    conn = psycopg2.connect(database='movies', user='postgres', password='postgres')

    #Create a client cursor to execute commands
    cursor = conn.cursor()

    #The variables placeholder must always be a %s, psycop2 will automatically convert the values to SQL literal
    query = "SELECT * FROM atrocities_details"
    movies = psql.read_sql(query, conn)

    movies["color"] = np.where(movies["gender"] == 'Male', "orange", "grey")
    movies["alpha"] = np.where(movies["gender"] == 'Male', 0.9, 0.25)
    movies.fillna(0, inplace=True)  # just replace missing values with zero

    axis_map = {
        "Date": "date_published",
        "State": "state",
        "Gender": "gender",
        "Religion": "religion",
        "Caste": "caste"
    }

    desc = Div(text=open(join(dirname(__file__), "description.html")).read(), width=800)

    # Create Input controls
    min_date = Slider(title="Date From", start=2000, end=2017, value=2010, step=1)
    max_date = Slider(title="Date To", start=2001, end=2018, value=2018, step=1)
    religion = Select(title="Religion", value="All",
                   options=open(join(dirname(__file__), 'religion.txt')).read().split())
    state = Select(title="State", value="All",
                   options=open(join(dirname(__file__), 'states.txt')).read().split())
    incident_name = TextInput(title="Incident name contains")
    x_axis = Select(title="X Axis", options=sorted(axis_map.keys()), value="Caste")
    y_axis = Select(title="Y Axis", options=sorted(axis_map.keys()), value="Date")

    # Create Column Data Source that will be used by the plot
    source = ColumnDataSource(data=dict(x=[], y=[], color=[], title=[], alpha=[]))

    hover = HoverTool(tooltips=[
        ("Title", "@title")
    ])

    p = figure(plot_height=500, plot_width=500, title="", toolbar_location=None, tools=[hover])
    p.circle(x="x", y="y", source=source, size=7, color="color", line_color=None, fill_alpha="alpha")

    def select_movies():
        religion_val = religion.value
        state_val = state.value
        incident_name_val = incident_name.value.strip()

        # Compare form details with the database object that we created above and return the result
        selected = movies[
            (movies.date_published >= str(min_date.value) + "-01-01 00:00:00") &
            (movies.date_published <= str(max_date.value) + "-01-01 00:00:00")
        ]

        if (religion_val != "All"):
            selected = selected[selected.religion.str.contains(religion_val)==True]
        if (state_val != "All"):
            selected = selected[selected.state.str.contains(state_val)==True]
        if (incident_name_val != ""):
            selected = selected[selected.title.str.contains(incident_name_val)==True]
        return selected

    def update():
        df = select_movies()
        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.title.text = "%d incident selected" % len(df)
        source.data = dict(
            x=df[x_name],
            y=df[y_name],
            color=df["color"],
            title=df["title"],
            alpha=df["alpha"]
        )

    controls = [religion, min_date, max_date, state, incident_name, x_axis, y_axis]
    for control in controls:
        control.on_change('value', lambda attr, old, new: update())

    sizing_mode = 'fixed'  # 'scale_width' also looks nice with this example

    inputs = widgetbox(*controls, sizing_mode=sizing_mode)
    l = layout([
        [desc],
        [inputs, p],
    ], sizing_mode=sizing_mode)

    update()  # initial load of the data

    curdoc().add_root(l)
    curdoc().title = "Movies"

    #print(cursor.fetchone())
except psycopg2.DatabaseError as e:
    print ('Error %s' % e)
    sys.exit(1)
finally:
    if conn:
        conn.close()