import glob, subprocess, csv

from config.settings import *
from settings import *
import reports
import util.db

def database_data():
    db = util.db.connect()
    cur = db.cursor()

    cur.execute("""select
two.datname as database,
two.numbackends as backends,
two.xact_commit - one.xact_commit as commits_diff,
two.xact_rollback - one.xact_rollback as rollbacks_diff,
two.blks_read - one.blks_read as blocks_read_diff,
two.blks_hit - one.blks_hit as blocks_hit_diff,
two.tup_returned - one.tup_returned as tuples_returned_diff,
two.tup_fetched - one.tup_fetched as tuples_fetched_diff,
two.tup_inserted - one.tup_inserted as tuples_inserted_dif,
two.tup_updated - one.tup_updated as tuples_updated_dif,
two.tup_deleted - one.tup_deleted as tuples_deleted_diff,
two.conflicts - one.conflicts as conflicts_diff
from server.pg_stat_database_start one
INNER JOIN server.pg_stat_database_end two on one.datid = two.datid;""")

    if cur.rowcount > 0:
        reports.text_report(cur, REPORT_DIR_PATH + '/database_details.csv')


def conflicts():
    db = util.db.connect()
    cur = db.cursor()

    cur.execute("""select * FROM server.pg_stat_database_conflicts_start one
    INNER JOIN server.pg_stat_database_conflicts_end two
    ON one.datid=two.datid WHERE one.confl_tablespace > 0 OR two.confl_tablespace > 0
                OR one.confl_lock > 0 OR two.confl_lock > 0
                OR one.confl_snapshot > 0 OR two.confl_snapshot > 0
                OR one.confl_bufferpin > 0 OR two.confl_bufferpin > 0
                OR one.confl_deadlock > 0 OR two.confl_deadlock > 0;""")

    if cur.rowcount > 0:
        reports.text_report(cur, REPORT_DIR_PATH + '/conflicts.csv')

def queries_app_name():
    db = util.db.connect()
    cur = db.cursor()

    cur.execute("""SELECT database_name, application_name, command_tag, count(*) from postgres_log WHERE command_tag IN
    ('INSERT','UPDATE','DELETE', 'SELECT') group by 1,2,3 order by 1,3,4 desc;""")

    if cur.rowcount > 0:
        reports.text_report(cur, REPORT_DIR_PATH + '/queries_by_application.csv')

def database_size():
        size_start_total = 0
        size_end_total=0
        filename = REPORT_DIR_PATH + '/database_size.csv'
        with open(filename, 'a') as db_sizes:
            fwriter = csv.writer(db_sizes,delimiter='\t',quoting=csv.QUOTE_NONNUMERIC)
            fwriter.writerow(('db name', 'db size start', 'db size end'))
            for SCHEMA_NAME in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:
                if SCHEMA_NAME == "server":
                    continue

                db = util.db.connect()

                cur = db.cursor()
                cur.execute("SET search_path=%s, public, server"%SCHEMA_NAME)
                cur.execute("select pg_size_pretty(info::bigint), info::bigint from other_stats_start where name = 'database size';")
                results_start=cur.fetchall()
                cur.execute("select pg_size_pretty(info::bigint), info::bigint from other_stats_end where name = 'database size';")
                results_end=cur.fetchall()
                size_start = [r[0] for r in results_start]
                size_end = [r[0] for r in results_end]
                try:
                    size_start_total +=int([r[1] for r in results_start][0])
                except:
                    pass
                try:
                    size_end_total +=int([r[1] for r in results_end][0])
                except:
                    pass
                try:
                    fwriter.writerow((SCHEMA_NAME, size_start[0], size_end[0]))
                except:
                    pass
            fwriter.writerow(('TOTAL(bytes)', size_start_total, size_end_total))
            fwriter.writerow(('TOTAL(GB)', size_start_total/1073741824, size_end_total/1073741824))

