import numpy as np
import matplotlib
from matplotlib.font_manager import FontProperties
from matplotlib.ticker import FuncFormatter
from matplotlib import dates
import psycopg2

from config.settings import *
from settings import *
import util.db
import reports.times
from util.matplotlib import magnitude_ticks
from util.log import log


matplotlib.use('Agg')
    
def statements_per_second():

    import matplotlib.pyplot as plot
    
    db = util.db.connect()

    cur = db.cursor()
    
    cur.execute("""
        SELECT measurement_time, select_count, insert_count, update_count, delete_count
            FROM statements_per_minute 
            WHERE measurement_time BETWEEN %(start_time)s AND %(end_time)s
            ORDER BY measurement_time
        """, reports.times.log_range())

    results = cur.fetchall()

    if not results:
        no_file = REPORT_DIR_PATH + '/statements_per_second.txt'
        with open(no_file, 'w') as no_results:
            no_results.write("No results.")
        no_results.close()
        log('No temp files recorded; skipping statements_per_second report')
        return

        
    timeseries = [r[0] for r in results]
    select_count = [float(r[1])/60 for r in results]
    insert_count = [float(r[2])/60 for r in results]
    update_count = [float(r[3])/60 for r in results]
    delete_count = [float(r[4])/60 for r in results]
    total_count = [s + i + u + d for s, i, u, d in zip(select_count, insert_count, update_count, delete_count)]

    fig = plot.figure(figsize=DEFAULT_FIGURE_SIZE)
    ax = fig.add_subplot(111, axisbg='#fafafa', alpha=0.9)

    hfmt = dates.DateFormatter('%H:%M:%S')
    ax.xaxis.set_major_formatter(hfmt)
    ax.set_xlim(xmin=reports.times.log_start(), xmax=reports.times.log_end())

    ax.plot(timeseries, select_count, color='blue', label='Select')
    ax.plot(timeseries, insert_count, color='green', label='Insert')
    ax.plot(timeseries, update_count, color='red', label='Update')
    ax.plot(timeseries, delete_count, color='black', label='Delete')
    ax.plot(timeseries, total_count, ':', color='red', label='Total')
   
    small_font = FontProperties()
    small_font.set_size('small')
    
    ax.set_title('Statements per Second')
    
    ax.set_ylabel('')
    
    lgnd = ax.legend(loc='upper right', bbox_to_anchor=(1.2, 1.0), prop=small_font)

    ax.grid(True)
    
    fig.autofmt_xdate()
    
    plot.savefig(REPORT_DIR_PATH + '/statements_per_second.pdf', bbox_inches='tight', pad_inches=0.25, bbox_extra_artists=[lgnd])

    plot.close()


def statement_durations_by_time():

    import matplotlib.pyplot as plot
    
    db = util.db.connect()

    cur = db.cursor()
    
    cur.execute("""
        SELECT measurement_time, max_duration, avg_duration
            FROM statements_per_minute 
            WHERE measurement_time BETWEEN %(start_time)s AND %(end_time)s
            ORDER BY measurement_time
        """, reports.times.log_range())

    results = cur.fetchall()

    if not results:
        no_file = REPORT_DIR_PATH + '/statement_durations_by_time.txt'
        with open(no_file, 'w') as no_results:
            no_results.write("No results.")
        no_results.close()
        log('No temp files recorded; skipping statement_durations_by_time report')
        return
        
    timeseries = [r[0] for r in results]
    max_duration = [float(r[1]) for r in results]
    avg_duration = [float(r[2]) for r in results]

    fig = plot.figure(figsize=DEFAULT_FIGURE_SIZE)
    ax = fig.add_subplot(111, axisbg='#fafafa', alpha=0.9)

    hfmt = dates.DateFormatter('%H:%M:%S')
    ax.xaxis.set_major_formatter(hfmt)
    ax.set_yscale('log')
    
    small_font = FontProperties()
    small_font.set_size('small')
    
    ax.set_title('Statement Duration (minute bins)')
    
    ax.set_ylabel('Milliseconds (log scale)')
    
    ax.grid(True)
    ax.yaxis.set_major_formatter(FuncFormatter(magnitude_ticks))
    
    ax.set_xlim(xmin=reports.times.log_start(), xmax=reports.times.log_end())

    ax.plot(timeseries, avg_duration, color='blue', label='Average')
    ax.plot(timeseries, max_duration, color='red', label='Maximum')
   
    lgnd = ax.legend(loc='upper right', bbox_to_anchor=(1.25, 1.0), prop=small_font)

    fig.autofmt_xdate()
    
    plot.savefig(REPORT_DIR_PATH + '/statement_durations.pdf', bbox_inches='tight', pad_inches=0.25, bbox_extra_artists=[lgnd])

    plot.close()
    
