#!/usr/bin/env python3

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

import plotly.graph_objects as go

import datetime as dt
from natsort import natsorted

import db
from app import app

def layout():
    return dbc.Container(fluid=True, children=[
        dbc.Row([
            dbc.Col(width=12, lg=1, children=[
                dcc.Dropdown(
                    id='machines-days',
                    options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]],
                    value=7,
                    clearable=False
                )
            ]),
            dbc.Col(width=12, lg=3, children=[
                dcc.DatePickerSingle(
                    id='machines-date',
                    date=get_newest_date(),
                    display_format='DD-MM-YYYY',
                    max_date_allowed=get_newest_date(),
                    initial_visible_month=get_newest_date(),
                    first_day_of_week=1
                ),
            ]),
            dbc.Col(width=12, lg=6, children=[
                dcc.Dropdown(
                    id='machines-satellites',
                    options=[{'label': s, 'value': s} for s in get_satellites()],
                    multi=True,
                    value=[],
                    placeholder='All Satellites'
                )
            ])
        ]),
        dbc.Row([dbc.Col([
            dcc.Loading(dcc.Graph(id='machines-graph-location'))
        ])]),
        dbc.Row([
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-ram'))
            ]),
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-realcores'))
            ]),
            dbc.Col(width=12, md=6, xl=4, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-kvmstate'))
            ]),
            dbc.Col(width=12, xl=6, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-cpumodel'))
            ]),
            dbc.Col(width=12, xl=6, children=[
                dcc.Loading(dcc.Graph(id='machines-graph-systemmodel'))
            ])
        ])
    ])

@app.callback(Output('machines-graph-location', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_location(days, date, satellites):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT m.value, m.count
            FROM reports r
            JOIN machine m ON r.id = m.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND m.days = %s AND m.property = 'location'
        """
    stmt_end = """ ORDER BY m.count DESC"""

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days] + satellites))
    else:
        cursor.execute(stmt + stmt_end, (date, days))

    data = cursor.fetchall()

    db.closeConnection(dbcon)

    figure = go.Figure()
    figure.add_trace(go.Bar(
        x=[item['value'][0:9] for item in data],
        y=[item['count'] for item in data]
    ))
    figure.update_layout(
        title_text = 'Locations'
    )
    return figure

@app.callback(Output('machines-graph-cpumodel', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_cpumodel(days, date, satellites):
    return make_graph(days, date, satellites, 'cpumodel', 'CPU', typ='bar', height=600)

@app.callback(Output('machines-graph-realcores', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_realcores(days, date, satellites):
    return make_graph(days, date, satellites, 'realcores', 'Cores', unit=' Cores')

@app.callback(Output('machines-graph-ram', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_ram(days, date, satellites):
    return make_graph(days, date, satellites, 'ram', 'RAM', unit=' GB')

@app.callback(Output('machines-graph-systemmodel', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_systemmodel(days, date, satellites):
    return make_graph(days, date, satellites, 'systemmodel', 'System Model', typ='bar', height=600)

@app.callback(Output('machines-graph-kvmstate', 'figure'),
              [Input('machines-days', 'value'),
               Input('machines-date', 'date'),
               Input('machines-satellites', 'value')])
def make_graph_kvmstate(days, date, satellites):
    return make_graph(days, date, satellites, 'kvmstate', 'KVM State')


def make_graph(days, date, satellites, prop, title, unit='', typ='pie', height=450):
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    stmt = """
            SELECT m.value, SUM(m.count) AS count
            FROM reports r
            JOIN machine m ON r.id = m.report
            WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
            AND m.days = %s AND m.property = %s
        """
    stmt_end = """ GROUP BY m.value"""

    if len(satellites) > 0:
        formatStrings = ','.join(['%s'] * len(satellites))
        cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days, prop] + satellites))
    else:
        cursor.execute(stmt + stmt_end, (date, days, prop))

    data = cursor.fetchall()
    data = natsorted(data, key = lambda x: x['value' if typ=='pie' else 'count'])

    db.closeConnection(dbcon)

    figure = go.Figure()
    if typ == 'pie':
        figure.add_trace(go.Pie(
            labels=[item['value'] + unit for item in data],
            values=[item['count'] for item in data],
            hole=0.3,
            sort=False,
            textinfo='label',
            direction='clockwise'
        ))
    elif typ == 'bar':
        figure.add_trace(go.Bar(
            y=[item['value'] + unit for item in data],
            x=[item['count'] for item in data],
            orientation='h'
        ))
    figure.update_layout(
        title_text = title,
        height = height,
        yaxis_automargin=True
    )
    return figure

def get_satellites():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT DISTINCT ip FROM reports""")

    data = [item['ip'] for item in cursor.fetchall()]

    db.closeConnection(dbcon)
    return data

def get_newest_date():
    dbcon = db.getConnection()
    cursor = dbcon.cursor()

    cursor.execute("""SELECT date FROM reports ORDER BY date DESC LIMIT 1""")

    data = cursor.fetchall()

    db.closeConnection(dbcon)
    return data[0]['date']