import glob, subprocess


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


from config.settings import *
from settings import *
import util.db
from util import table_scale
from util.matplotlib import magnitude_ticks
from util.log import log

matplotlib.use('Agg')

def table_sizes():
    import matplotlib.pyplot as plot
    subprocess.call(["mkdir", REPORT_DIR_PATH + "/table-sizes"])
    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        xx_small_font = FontProperties()
        xx_small_font.set_size('xx-small')

        x_small_font = FontProperties()
        x_small_font.set_size('x-small')

        db = util.db.connect()

        cur = db.cursor()

        cur.execute("SET search_path=%s, public"%SCHEMA_NAME)
        cur.execute("SELECT * FROM table_sizes() WHERE total_size>0 AND data_size > 0 ORDER BY total_size DESC LIMIT 20")

        results = cur.fetchall()

        if not results:
            no_file = REPORT_DIR_PATH + '/table-sizes/table_sizes_noresults.txt'
            with open(no_file, 'a') as no_results:
                no_results.write("No results for %s.\n"%SCHEMA_NAME)
            no_results.close()
            log('No data recorded; skipping table_sizes report')
            continue

        table_name = [ r[0] for r in results ]
        data_size = [ float(r[1]) for r in results ]
        index_toast_size = [ float(r[2]) for r in results ]

        table_name.reverse()
        data_size.reverse()
        index_toast_size.reverse()

        if not data_size:
            with open (REPORT_DIR_PATH + '/table-sizes/table-sizes-%snone.txt'%SCHEMA_NAME, 'w') as nodata:
                strdata_size = str(data_size)
                nodata.write("No elements where pg_relation_size and pg_total_relation_size are both greater than 0. \n data_size = " + strdata_size)
            nodata.close()
            continue
        ind = np.arange(len(table_name))

        width = 0.35

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

        ax.set_title('Largest Tables -- %s'%SCHEMA_NAME)

        ax.set_xlabel('Size in bytes (log scale)')

        ax.set_xscale('log')
        ax.grid(True)

        ax.xaxis.set_major_formatter(FuncFormatter(magnitude_ticks))


        dbar = ax.barh(ind, data_size, width, left=util.log_axis_scale(data_size), linewidth=0, color='blue', label='Main Data')
        ibar = ax.barh(ind, index_toast_size, width, left=data_size, linewidth=0, color='red', label='Toast/Indexes')
        ax.set_yticks(ind + width/2)
        ax.set_yticklabels(table_name, fontproperties=xx_small_font)

        ax.legend(loc='lower right', prop=x_small_font)

        plot.tight_layout()

        plot.savefig(REPORT_DIR_PATH + '/table-sizes/table-sizes-%s.pdf'%SCHEMA_NAME)

        plot.close()



def table_growth():
    import matplotlib.pyplot as plot
    subprocess.call(["mkdir", REPORT_DIR_PATH + "/table-growth"])

    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        xx_small_font = FontProperties()
        xx_small_font.set_size('xx-small')

        x_small_font = FontProperties()
        x_small_font.set_size('x-small')

        db = util.db.connect()

        cur = db.cursor()

        cur.execute("SET search_path=%s, public"%SCHEMA_NAME)
        cur.execute("SELECT * FROM table_growth() WHERE growth>0 ORDER BY growth DESC LIMIT 10")

        results = cur.fetchall()

        if not results:
            no_file = REPORT_DIR_PATH + '/table-growth/table_growth_noresults.txt'
            with open(no_file, 'a') as no_results:
                no_results.write("No results for %s.\n"%SCHEMA_NAME)
            no_results.close()
            log('No data recorded; skipping table_growth report')
            continue

        table_name = [ r[0] for r in results ]
        growth = [ float(r[1]) for r in results ]

        table_name.reverse()
        growth.reverse()

        if not growth:
            with open (REPORT_DIR_PATH + '/table-growth/table-growth-%s_none.txt'%SCHEMA_NAME, 'w') as nodata:
                strdata_size = str(growth)
                nodata.write("No elements where growth greater than 0. \n data_size = " + strdata_size)
            nodata.close()
            continue

        ind = np.arange(len(table_name))

        width = 0.35

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

        ax.set_title('Fastest-Growing Tables -- %s'%SCHEMA_NAME)

        ax.set_xlabel('Growth in bytes per Hour (log scale)')

        ax.set_xscale('log')
        ax.grid(True)

        ax.xaxis.set_major_formatter(FuncFormatter(magnitude_ticks))

        dbar = ax.barh(ind, growth, width, left=util.log_axis_scale(growth), linewidth=0, color='blue', label='Main Data')
        ax.set_yticks(ind + width/2)
        ax.set_yticklabels(table_name, fontproperties=xx_small_font)

        plot.tight_layout()

        plot.savefig(REPORT_DIR_PATH + '/table-growth/table-growth-%s.pdf'%SCHEMA_NAME)

        plot.close()



def tuples_read():
    import matplotlib.pyplot as plot
    subprocess.call(["mkdir", REPORT_DIR_PATH + "/tuples-read"])

    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        xx_small_font = FontProperties()
        xx_small_font.set_size('xx-small')

        x_small_font = FontProperties()
        x_small_font.set_size('x-small')

        db = util.db.connect()

        cur = db.cursor()
        cur.execute("SET search_path=%s, public"%SCHEMA_NAME)
        cur.execute("SELECT * from table_activity() WHERE tuples_read>0 ORDER BY tuples_read DESC LIMIT 20")

        results = cur.fetchall()

        if not results:
            no_file = REPORT_DIR_PATH + '/tuples-read/tuples_read.txt'
            with open(no_file, 'a') as no_results:
                no_results.write("No results for %s.\n"%SCHEMA_NAME)
            no_results.close()
            log('No data recorded; skipping tuples_read report')
            continue

        table_name = [ r[0] for r in results ]
        tuples_read = [ float(r[1]) for r in results ]

        table_name.reverse()
        tuples_read.reverse()

        if not tuples_read:
            with open (REPORT_DIR_PATH + '/tuples-read/tuples-read-%snone.txt'%SCHEMA_NAME, 'w') as nodata:
                strdata_size = str(tuples_read)
                nodata.write("No elements where tuples_read greater than 0. \n data_size = " + strdata_size)
            nodata.close()
            continue
        ind = np.arange(len(table_name))

        width = 0.35

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

        ax.set_title('Most-Read Tables -- %s'%SCHEMA_NAME)

        ax.set_xlabel('Tuples (log scale)')

        ax.set_xscale('log')
        ax.grid(True)

        ax.xaxis.set_major_formatter(FuncFormatter(magnitude_ticks))

        dbar = ax.barh(ind, tuples_read, width, left=util.log_axis_scale(tuples_read), linewidth=0, color='green', label='Main Data')
        ax.set_yticks(ind + width/2)
        ax.set_yticklabels(table_name, fontproperties=xx_small_font)

        plot.tight_layout()

        plot.savefig(REPORT_DIR_PATH + '/tuples-read/tuples-read-%s.pdf'%SCHEMA_NAME)

        plot.close()



def tuples_written():
    import matplotlib.pyplot as plot
    subprocess.call(["mkdir", REPORT_DIR_PATH + "/tuples-written"])

    for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
        if SCHEMA_NAME == "server":
            continue
        xx_small_font = FontProperties()
        xx_small_font.set_size('xx-small')

        x_small_font = FontProperties()
        x_small_font.set_size('x-small')

        db = util.db.connect()

        cur = db.cursor()

        cur.execute("SET search_path=%s, public"%SCHEMA_NAME)
        cur.execute("SELECT * from table_activity() WHERE tuples_written>0 ORDER BY tuples_written DESC LIMIT 20")

        results = cur.fetchall()

        if not results:
            no_file = REPORT_DIR_PATH + '/tuples-written/tuples_written.txt'
            with open(no_file, 'a') as no_results:
                no_results.write("No results for %s.\n"%SCHEMA_NAME)
            no_results.close()
            log('No data recorded; skipping tuples_written report')
            continue

        table_name = [ r[0] for r in results ]
        tuples_inserted = [ max(0.1, float(r[3])) for r in results ]
        tuples_updated = [ max(0.1, float(r[4])) for r in results ]
        tuples_deleted = [ max(0.1, float(r[5])) for r in results ]

        table_name.reverse()
        tuples_inserted.reverse()
        tuples_updated.reverse()
        tuples_deleted.reverse()

        if not table_name:
            with open (REPORT_DIR_PATH + '/tuples-written/tuples-written-%snone.txt'%SCHEMA_NAME, 'w') as nodata:
                strdata_size = str(tuples_read)
                nodata.write("No elements where tuples_written greater than 0. \n data_size = " + strdata_size)
            nodata.close()
            continue

        ind = np.arange(len(table_name))

        width = 0.35

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

        ax.set_title('Most-Written Tables -- %s'%SCHEMA_NAME)

        ax.set_xlabel('Tuples (log scale)')
        ax.set_xlim(xmin=(tuples_inserted[0] + tuples_updated[0] + tuples_deleted[0])*0.1,
            xmax=(tuples_inserted[-1] + tuples_updated[-1] + tuples_deleted[-1])*1.3)
        ax.set_xscale('log')
        ax.grid(True)

        ax.xaxis.set_major_formatter(FuncFormatter(magnitude_ticks))

        ibar = ax.barh(ind, tuples_inserted, width, left=util.log_axis_scale(tuples_inserted), linewidth=0, color='green', label='Inserted')
        ubar = ax.barh(ind, tuples_updated, width, left=tuples_inserted, linewidth=0, color='blue', label='Updated')
        dbar = ax.barh(ind, tuples_deleted, width,
            left=[ x + y for x, y  in zip(tuples_inserted, tuples_updated)], linewidth=0, color='#666666', label='Deleted')
        ax.set_yticks(ind + width/2)
        ax.set_yticklabels(table_name, fontproperties=xx_small_font)

        ax.legend(loc='lower right', prop=x_small_font)

        plot.tight_layout()

        plot.savefig(REPORT_DIR_PATH + '/tuples-written/tuples-written-%s.pdf'%SCHEMA_NAME)

        plot.close()

