import dash
import dash_table
from dash.dependencies import Input, Output, State
import dash_html_components as html
import dash_core_components as dcc
import pandas as pd
from flask import render_template
import plotly.express as px
import plotly.graph_objs as go
import sqlite3

"""def generate_table(dataframe, max_rows=10):
        return html.Table([html.Thead(
            html.Tr([html.Th(col) for col in dataframe.columns])),
            html.Tbody([html.Tr([html.Td(dataframe.iloc[i][col]) for col in dataframe.columns]) for i in range(min(len(dataframe), max_rows))])])
"""
# Initiate HTML style
#external_stylesheets = ['https://codepen.io/chriddyp/pen/bWLwgP.css'];
app = dash.Dash(__name__);  # external_stylesheets=external_stylesheets);

try:
    # Connect to database
    db_connection = sqlite3.connect("../../D3-Centraliser/annotations.db");
    db_cursor = db_connection.cursor();

    # Get data from database
    raw = db_cursor.execute("SELECT url from webpage;");
    #df = pd.read_sql_query("SELECT webpage.url,categories.category FROM webpage,categories;", db_connection);
    df = pd.read_sql_query("select distinct categories.category,webpage.sha256,webpage.url,webpage.uuid from categories LEFT JOIN webpage ON webpage.sha256 = categories.sha256;", db_connection);
    df2 = pd.read_sql_query("select distinct categories.category,webpage.sha256,webpage.url from categories LEFT JOIN webpage ON webpage.sha256 = categories.sha256;", db_connection);

    # Read Fleiss and Cohen's kappa scores
    df3 = pd.read_sql_query("SELECT categories.category, categories.cohen_kappa_score FROM categories;", db_connection);

    # Frequency Y axis
    count = df2["category"].value_counts()
    fig2 = px.bar(count, y='category', x=count.index, color="category", title="Number of samples per category in DB")
    fig2.update_layout(plot_bgcolor='#111111', paper_bgcolor='#111111', font_color="white")

    # Get static descriptive statistics
    desc_query = "SELECT * from webpage;";

    # Draw the entire HTML page
    app.layout = html.Div(children=[html.Title('D3-Visualiser - Dark Web Monitoring'), html.Img(src='/assets/PDTOR_Logo_YELLOW.png'), html.H1("Recently Added .onion URLs"), \
        dash_table.DataTable(id='table', style_table={'textAlign':'right','height':'auto'}, columns=[{"name": i, "id":i} for i in df.columns], fill_width=True, \
            fixed_rows={'headers': True}, style_as_list_view=True, style_header={'backgroundColor':'rgb(30,30,30)', 'textAlign':'center'}, \
            style_cell={'backgroundColor':'rgb(30,30,30)','color':'white', 'textAlign':'center', 'height':'auto'}, data=df.to_dict('records')), \
            html.Br(),html.Br(), \
        dash_table.DataTable(id="table2", style_table={'textAlign':'right','height':'auto'}, columns=[{"name": y, "id": y} for y in df3.columns], fill_width=True,\
            fixed_rows={'headers': True}, style_as_list_view=True, style_header={'backgroundColor':'rgb(30,30,30)', 'textAlign':'center'}, \
            style_cell={'backgroundColor':'rgb(30,30,30)','color':'white', 'textAlign':'center', 'height':'auto'}, data=df3.to_dict('records')), \
        html.H1("Categorised Webpages Currently in Database"), dcc.Graph(id='number-of-categories', figure=fig2),\
        html.H2("<hr>(TBD) Export Database to .csv"), \
        html.H2("<hr>(TBD) Browse archived .onion web pages.")]);

    db_connection.commit();
    db_connection.close();

except sqlite3.Error as err:
    print("Database error: ", err);


if __name__ == '__main__':
    # Start web server 
    app.run_server("10.11.3.105", 8050,debug=True);
    #from waitress import serve
    #serve(app, host="10.1.100.17", port=8050)