from flask import Flask, render_template, request, url_for


from numbers import Number
# from datetime import datetime
import pandas as pd
import json

try:
    import altair
    _ALTAIR = True
except ImportError:
    _ALTAIR = False

from .. import store

from . import settings


def numlike(x):
    return isinstance(x, Number)


session = store.Session()

app = Flask(__name__)
app.config.from_object(settings)
app.config.from_envvar('STOREVIEW_SETTINGS', silent=True)

if 'DB_FILE' in app.config:
    print('Setting DB_FILE:', app.config['DB_FILE'])
    store.DB_FILE = app.config['DB_FILE']


def db_total_stats():
    session = store.Session()
    df = pd.DataFrame(
        session.execute("""
            SELECT relname AS "relation",
                pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
            FROM pg_class C
            LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
            WHERE nspname NOT IN ('pg_catalog', 'information_schema')
                AND C.relkind <> 'i'
                AND nspname !~ '^pg_toast'
                AND relname !~ 'id_seq'
            ORDER BY pg_total_relation_size(C.oid) DESC
            LIMIT 20;
            """).fetchall(), columns=['tabel name', 'total size']
    )
    session.close()
    return df


def db_user_stats():
    session = store.Session()
    df = pd.DataFrame(
        session.execute("""
            SELECT simulations.user, COUNT(DISTINCT simulations),
               pg_size_pretty(SUM(pg_column_size(data))) as "data-size",
               pg_size_pretty(SUM(pg_column_size(data)) / COUNT(DISTINCT simulations)) as "size / sim"
             FROM evaluations
             JOIN simulations 
             ON (simulations.id = evaluations.simulation_id)
             GROUP BY simulations.user
             ORDER BY count DESC;
        """).fetchall(), columns=['user', '# of simulations', 'total data', 'data per simulation']
    )
    session.close()
    return df


def db_eval_stats():
    session = store.Session()
    df = pd.DataFrame(
        session.execute("""
            SELECT observable, 
              pg_size_pretty(ROUND(AVG(pg_column_size(data)), 0)) as "size-avg",
              pg_size_pretty(ROUND(SUM(pg_column_size(data)), 0)) as "size-total",
              COUNT(*), AVG(pg_column_size(data)) as "size_bytes"
            FROM evaluations
            GROUP BY observable
            ORDER BY size_bytes DESC;
        """).fetchall(), columns=['observable', 'Avg. size', 'Total size', '# of evaluations', 'size_bytes']
    ).drop('size_bytes', axis=1)
    session.close()
    return df[~df['Total size'].isnull()]


@app.route('/')
def view_simulations():
    session = store.Session()
    simulations = store.query_simulation(
        directory='%' + request.args.get('directory', '') + '%',
        user=request.args.get('user', '') + '%',
        session=session
    ).all()
    r = render_template('simulations.html', simulations=simulations)
    session.close()
    return r


@app.route('/statistics')
def view_statistics():
    return render_template('statistics.html', user_stats=db_user_stats(), 
                           total_stats=db_total_stats(), eval_stats=db_eval_stats())


@app.route('/simulation/<int:sim_id>')
def view_simulation(sim_id):
    session = store.Session()
    simulation = store.query_simulation(session=session).filter(store.Simulation.id == sim_id).first()
    params = simulation.string_params + simulation.float_params
    evaluations = list(session.query(store.Evaluation).filter(
        store.Evaluation.simulation_id == simulation.id).order_by('observable').values(
            'id', 'observable', 'selection'
        )
    )
    r = render_template('simulation-details.html', simulation=simulation,
                           sim_params=params, evaluations=evaluations)
    session.close()
    return r

@app.route('/evaluation/<int:id>')
def view_evaluation(id):
    session = store.Session()
    evaluation = session.query(store.Evaluation).filter(store.Evaluation.id == id).first()
    df = evaluation.data
    if numlike(df):
        data = df
    elif df is not None:
        data_columns = list(df.columns)
        for col in ['directory', 'T', 'user', 'selection', 'system', 'ensemble']:
            if col in data_columns:
                data_columns.remove(col)
        data = df[data_columns]
    else:
        data = None
    simulation = evaluation.simulation
    sim_params = simulation.string_params + simulation.float_params
    r = render_template('evaluation-details.html', simulation=simulation, evaluation=evaluation,
                           sim_params=sim_params, evaluation_params=evaluation.parameters, data=data)
    session.close()
    return r


@app.route('/evaluation/<int:id>/data.csv')
def get_evaluation_data(id):
    session = store.Session()
    evaluation = session.query(store.Evaluation).filter(store.Evaluation.id == id).first()
    df = evaluation.data
    if numlike(df):
        data = df
    else:
        data_columns = list(df.columns)
        for col in ['directory', 'T', 'user', 'selection', 'system', 'ensemble']:
            if col in data_columns:
                data_columns.remove(col)
        data = df[data_columns]
    session.close()
    return data.to_csv()


@app.route('/evaluation/<int:id>/spec.json')
def get_evaluation_plot_spec(id):
    session = store.Session()
    evaluation = session.query(store.Evaluation).filter(store.Evaluation.id == id).first()
    df = evaluation.data
    if numlike(df) or not _ALTAIR:
        return '{}'
    else:
        data_columns = list(df.columns)
        for col in ['directory', 'T', 'user', 'selection', 'system', 'ensemble']:
            if col in data_columns:
                data_columns.remove(col)
    plot_spec = {}
    x = y = None
    for col in ['time', 'radius', 'r']:
        if col in data_columns:
            ycol = data_columns[(data_columns.index(col) + 1) % 2]
            if col == 'time':
                x = altair.X(col + ':Q', scale=altair.Scale(type='log'))
            else:
                x = altair.X(col + ':Q')
            if ycol == 'msd':
                y = altair.Y(ycol + ':Q', scale=altair.Scale(type='log'))
            else:
                y = altair.Y(ycol + ':Q')

    if x is None:
        xcol, ycol = data_columns[:2]
        x = altair.X(xcol + ':Q')
        y = altair.Y(ycol + ':Q')

    ch = altair.Chart(
        url_for('get_evaluation_data', id=id),
        # data,
        width=300, height=200).mark_point().encode(
        x=x, y=y)
    plot_spec = ch.to_dict()
    plot_spec["$schema"] = "https://vega.github.io/schema/vega-lite/v1.json"
    json.codecs
    session.close()
    return json.dumps(plot_spec)