# !/usr/bin/python
# -*- coding: utf-8 -*-

import dash
import dash_core_components as dcc
import dash_html_components as html
from dash.dependencies import Input, Output

import plotly.graph_objs as go

from datetime import datetime
import numpy as np
import pandas as pd

# задаём данные для отрисовки
from sqlalchemy import create_engine

db_config = {'user': 'my_user',
            'pwd': 'my_user_password',
            'host': 'localhost',
            'port': 5432,
            'db': 'zen'}
engine = create_engine('postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
                                                           db_config['pwd'],
                                                           db_config['host'],
                                                           db_config['port'],
                                                           db_config['db']))
# получаем сырые данные
query_visits =  '''
                SELECT * FROM dash_visits
                '''

query_engagement = '''
                   SELECT * FROM dash_engagement
                   '''
# записываем их в таблицу
dash_visits = pd.io.sql.read_sql(query_visits, con = engine)
dash_engagement = pd.io.sql.read_sql(query_engagement, con = engine)

# приводим тип данных в dt
dash_visits['dt'] = pd.to_datetime(dash_visits['dt'])
dash_engagement['dt'] = pd.to_datetime(dash_engagement['dt'])

age_groups = dash_visits['age_segment'].unique()

note = '''
          Дашборд по визитам и вовлечённости пользователей.
       '''

external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css']
app = dash.Dash(__name__, external_stylesheets=external_stylesheets,compress=False)
app.config['suppress_callback_exceptions'] = False

app.layout = html.Div(children=[
    # формируем html
    html.H1(children = 'Дашборд для Яндекс.Дзена'),
    # пояснения
    html.Label(note),
    html.Br(),

    # вывод фильтров и селекторов
    html.Div([
        html.Div([
            html.Label('Фильтр даты и времени:'),
            dcc.DatePickerRange(
                start_date = dash_visits['dt'].min(),
                end_date = dash_visits['dt'].max(),
                display_format = 'YYYY-MM-DD MM:HH',
                id = 'dt_selector',
            ),

            html.Label('Фильтр возрастных категорий:'),
            dcc.Dropdown(
                options = [{'label': x, 'value': x} for x in dash_visits['age_segment'].unique()],
                value = dash_visits['age_segment'].unique().tolist(),
                multi = True,
                id = 'age_dropdown',
            ),
        ], className = 'six columns'),

        html.Div([
            html.Label('Фильтр тем карточек:'),
            dcc.Dropdown(
                options = [{'label': x, 'value': x} for x in dash_visits['item_topic'].unique()],
                value = dash_visits['item_topic'].unique().tolist(),
                multi = True,
                id = 'item_topic_dropdown',
            ),
        ], className = 'six columns')
    ], className = 'row'),

    # отрисовка графиков
    html.Div([
        html.Div([
            # график разбивки событий по темам источников
            html.Label('График событий во времени:'),
            dcc.Graph(
                style = {'height': '50vw'},
                id = 'history_absolute_visits'
            ),
        ], className = 'six columns'),

        html.Div([
            # график средней глубины взаимодействия
            html.Label('Круговая диаграмма событий:'),
            dcc.Graph(
                style = {'height': '25vw'},
                id = 'pie_visits'
            ),

            # график истории событий по темам карточек
            html.Label('Столбчатая диаграмма глубины взаимодействия:'),
            dcc.Graph(
                style = {'height': '25vw'},
                id = 'engagement_graph'
            ),
        ], className = 'six columns'),
    ], className = 'row'),

]) # начинается на Div(children=

@app.callback(
        [Output('history_absolute_visits', 'figure'),
         Output('pie_visits', 'figure'),
         Output('engagement_graph', 'figure')],
        [Input('item_topic_dropdown', 'value'),
         Input('age_dropdown', 'value'),
         Input('dt_selector', 'start_date'),
         Input('dt_selector', 'end_date')]
        )

def update_figures(selected_item_topics, selected_ages, start_date, end_date):
    query_filter = 'item_topic.isin(@selected_item_topics) and \
                    dt >= @start_date and dt <= @end_date \
                    and age_segment.isin(@selected_ages)'

    filtered_visits = dash_visits.query(query_filter)
    filtered_engagement = dash_engagement.query(query_filter)

    # точечная диаграмма
    agg_filtered_visits = filtered_visits.groupby(['item_topic', 'dt'], asindex=False).agg({'visits': 'sum'})
    scatterplot = []
    for topic in agg_filtered_visits['item_topic'].unique():
        scatterplot += [go.Scatter(x = agg_filtered_visits.query('item_topic == @topic')['dt'],
                                   y = agg_filtered_visits.query('item_topic == @topic')['visits'],
                                   mode = 'lines',
                                   stackgroup = 'one',
                                   name = topic)]

    # круговая диаграмма
    agg_filtered_visits_topic = filtered_visits.groupby('source_topic', asindex=False).agg({'visits': 'sum'})
    pie_by_topic = [go.Pie(labels = agg_filtered_visits_topic['source_topic'],
                           values = agg_filtered_visits_topic['visits'])]

    # столбчатая диаграмма
    agg_filtered_engagement = filtered_engagement.groupby('event', asindex=False).agg({'unique_users':'mean'})
    agg_filtered_engagement.rename(columns={'unique_users':'avg_unique_users'}, inplace=True)
    bar_by_event = go.Bar(x = report['event'],
                          y = report['avg_unique_users'])

    # формируем результат для отображения
    return (
        {
            'data': scatterplot,
            'layout': go.Layout(xaxis={'title': 'Дата и время'},
                                yaxis={'title': 'Визиты'})
        },
        {
            'data': pie_by_topic,
            'layout': go.Layout()
        },
        {
            'data': bar_by_event,
            'layout': go.Layout(xaxis={'title': 'Событие'},
                                yaxis={'title': 'Среднее кол-во уников'})
        },
    )
# описываем логику дашборда
if __name__ == '__main__':
    app.run_server(debug = True, host='0.0.0.0')