import os, subprocess
from time import sleep
import psycopg2


from config.settings import *
from settings import *
import util.db
from util.log import log
from util.parser import parse_pg_statistic_start


def load_monitor():

    db = util.db.connect()
    cur = db.cursor()
    for schema in [dir[1] for dir in os.walk(MONITOR_PATH_NAME)][0]:

        if schema == "server":
            create_begin_server_collection = """ begin;
        create schema server;
        CREATE table server.pg_stat_bgwriter_start(now timestamp with time zone, checkpoints_timed bigint, checkpoints_req bigint, buffers_checkpoint bigint, buffers_clean bigint, maxwritten_clean bigint, buffers_backend bigint, buffers_backend_fsync bigint, buffers_alloc bigint, stats_reset timestamp with time zone );
        CREATE table server.pg_stat_database_start(datid oid, datname name, numbackends integer, xact_commit bigint, xact_rollback bigint, blks_read bigint, blks_hit bigint, tup_returned bigint, tup_fetched bigint, tup_inserted bigint, tup_updated bigint, tup_deleted bigint, conflicts bigint, stats_reset timestamp with time zone);
        CREATE table server.pg_stat_activity_start(datid oid, datname name, pid integer, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp with time zone, xact_start timestamp with time zone, query_start timestamp with time zone, state_change timestamp with time zone, waiting boolean, state text, query text);        CREATE table server.current_pg_settings(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text, enumvals text[], boot_val text, reset_val text, sourcefile text, sourceline integer);
        CREATE table server.current_pg_stat_replication(pid integer, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp with time zone, state text, sent_location text, write_location text, flush_location text, replay_location text, sync_priority integer, sync_state text);
        CREATE table server.current_pg_am(oid oid, amname name, amstrategies smallint, amsupport smallint, amcanorder boolean, amcanbackward boolean, amcanunique boolean, amcanmulticol boolean, amoptionalkey boolean, amsearchnulls boolean, amstorage boolean, amclusterable boolean, amkeytype oid, aminsert regproc, ambeginscan regproc, amgettuple regproc, amgetbitmap regproc, amrescan regproc, amendscan regproc, ammarkpos regproc, amrestrpos regproc, ambuild regproc, ambulkdelete regproc, amvacuumcleanup regproc, amcostestimate regproc, amoptions regproc);
        CREATE table server.pg_stat_database_conflicts_start(datid oid, datname name, confl_tablespace bigint, confl_lock bigint, confl_snapshot bigint, confl_bufferpin bigint, confl_deadlock bigint);
        CREATE table server.pg_database_size_start(datname name, size text);
        end; """

            create_end_server_collection = """ begin;
        CREATE table server.pg_stat_bgwriter_end(current_setting text, now timestamp with time zone, checkpoints_timed bigint, checkpoints_req bigint, buffers_checkpoint bigint, buffers_clean bigint, maxwritten_clean bigint, buffers_backend bigint, buffers_backend_fsync bigint, buffers_alloc bigint, stats_reset timestamp with time zone);
        CREATE table server.pg_stat_database_end(datid oid, datname name, numbackends integer, xact_commit bigint, xact_rollback bigint, blks_read bigint, blks_hit bigint, tup_returned bigint, tup_fetched bigint, tup_inserted bigint, tup_updated bigint, tup_deleted bigint, conflicts bigint, stats_reset timestamp with time zone);
        CREATE table server.pg_stat_activity_end(datid oid, datname name, pid integer, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp with time zone, xact_start timestamp with time zone, query_start timestamp with time zone, state_change timestamp with time zone, waiting boolean, state text, query text);        CREATE table server.pg_stat_database_conflicts_end(datid oid, datname name, confl_tablespace bigint, confl_lock bigint, confl_snapshot bigint, confl_bufferpin bigint, confl_deadlock bigint);
        CREATE table server.pg_database_size_end(datname name, size text);
        end; """

            log("Creating begining server collection tables.")
            executed = cur.execute(create_begin_server_collection)
            if not executed == None:
                log("Database starting server data did not load." + executed)
            log("Creating ending server collection tables.")
            executed = cur.execute(create_end_server_collection)
            if not executed == None:
                log("Database ending server data did not load." + executed)

        else:
            create_begin_databases_tables = """ begin;
            create schema {schema};
            CREATE table {schema}.pg_stat_user_tables_start(dbname name, relid oid, schemaname name, relname name, seq_scan bigint, seq_tup_read bigint, idx_scan bigint, idx_tup_fetch bigint,n_tup_ins bigint, n_tup_upd bigint, n_tup_del bigint, n_tup_hot_upd bigint, n_live_tup bigint, n_dead_tup bigint, last_vacuum timestamp with time zone, last_autovacuum timestamp with time zone,last_analyze timestamp with time zone, last_autoanalyze timestamp with time zone, vacuum_count bigint, autovacuum_count bigint, analyze_count bigint, autoanalyze_count bigint);
            CREATE table {schema}.pg_stat_sys_tables_start(dbname name, relid oid, schemaname name, relname name, seq_scan bigint, seq_tup_read bigint, idx_scan bigint, idx_tup_fetch bigint,n_tup_ins bigint, n_tup_upd bigint, n_tup_del bigint, n_tup_hot_upd bigint, n_live_tup bigint, n_dead_tup bigint, last_vacuum timestamp with time zone, last_autovacuum timestamp with time zone,last_analyze timestamp with time zone, last_autoanalyze timestamp with time zone, vacuum_count bigint, autovacuum_count bigint, analyze_count bigint, autoanalyze_count bigint);
            CREATE table {schema}.pg_statio_user_tables_start(dbname name, relid oid, schemaname name, relname name, heap_blks_read bigint, heap_blks_hit bigint, idx_blks_read bigint, idx_blks_hit bigint, toast_blks_read bigint, toast_blks_hit bigint, tidx_blks_read bigint, tidx_blks_hit bigint);
            CREATE table {schema}.pg_statio_sys_tables_start(dbname name, relid oid, schemaname name, relname name, heap_blks_read bigint, heap_blks_hit bigint,  idx_blks_read bigint, idx_blks_hit bigint, toast_blks_read bigint, toast_blks_hit bigint, tidx_blks_read bigint, tidx_blks_hit bigint);
            CREATE table {schema}.pg_statio_user_indexes_start(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_blks_read bigint, idx_blks_hit bigint);
            CREATE table {schema}.pg_statio_sys_indexes_start(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_blks_read bigint, idx_blks_hit bigint);
            CREATE table {schema}.pg_stat_user_indexes_start(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint);
            CREATE table {schema}.pg_stat_sys_indexes_start(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint);
            CREATE table {schema}.pg_namespace_start(dbname name, oid oid, nspname name, nspowner oid);
            CREATE table {schema}.pg_stats_start(dbname name, schemaname name, tablename name, attname name, inherited boolean, null_frac real, avg_width integer, n_distinct real, most_common_freqs real[], correlation real);
            CREATE table {schema}.pg_stat_user_functions_start(dbname name, funcid oid, schemaname name, funcname name, calls bigint, total_time double precision, self_time double precision);
            CREATE table {schema}.other_stats_start(name TEXT, info TEXT);
            CREATE table {schema}.table_size_start(relid oid, schemaname name, relname name, pg_relation_size bigint, pg_total_relation_size bigint);
            CREATE table {schema}.pg_class_start(oid oid, relname name, relnamespace oid, reltype oid, reloftype oid, relowner oid, relam oid, relfilenode oid, reltablespace oid, relpages integer, reltuples real, reltoastrelid oid, reltoastidxid oid, relhasindex boolean , relisshared boolean, relpersistence "char", relkind "char", relnatts smallint, relchecks smallint, relhasoids boolean, relhaspkey boolean, relhasrules boolean, relhastriggers boolean, relhassubclass boolean , relfrozenxid xid, reloptions text[], pg_relation_size bigint);
            CREATE table {schema}.pg_index_start(indexrelid oid, indrelid oid, indnatts smallint, indisunique boolean, indisprimary boolean, indisexclusion boolean, indimmediate boolean, indisclustered boolean, indisvalid boolean, indcheckxmin boolean, indisready boolean, indkey int2vector, indcollation oidvector, indclass oidvector, indoption int2vector, indexprs text, indpred text);
            CREATE table {schema}.times(text text, now timestamp with time zone);
            CREATE table {schema}.pg_index_size_start(indexrelid oid, schemaname name, relname name, indexrelname name, index_size bigint);
            CREATE table {schema}.pg_indexes_start(dbname name,schemaname name, tablename name, indexname name, tablespace name, indexdef text);
            CREATE table {schema}.information_schema_columns_start(dbname name, table_catalog information_schema.sql_identifier, table_schema information_schema.sql_identifier, table_name information_schema.sql_identifier, column_name information_schema.sql_identifier, ordinal_position information_schema.cardinal_number, column_default information_schema.character_data, is_nullable information_schema.yes_or_no, data_type information_schema.character_data, character_maximum_length information_schema.cardinal_number, character_octet_length information_schema.cardinal_number, numeric_precision information_schema.cardinal_number, numeric_precision_radix information_schema.cardinal_number, numeric_scale information_schema.cardinal_number, datetime_precision information_schema.cardinal_number, interval_type information_schema.character_data, interval_precision information_schema.cardinal_number, character_set_catalog information_schema.sql_identifier, character_set_schema information_schema.sql_identifier, character_set_name information_schema.sql_identifier, collation_catalog information_schema.sql_identifier, collation_schema information_schema.sql_identifier, collation_name information_schema.sql_identifier, domain_catalog information_schema.sql_identifier, domain_schema information_schema.sql_identifier, domain_name information_schema.sql_identifier, udt_catalog information_schema.sql_identifier, udt_schema information_schema.sql_identifier, udt_name information_schema.sql_identifier, scope_catalog information_schema.sql_identifier, scope_schema information_schema.sql_identifier, scope_name information_schema.sql_identifier, maximum_cardinality information_schema.cardinal_number, dtd_identifier information_schema.sql_identifier, is_self_referencing information_schema.yes_or_no, is_identity information_schema.yes_or_no, identity_generation information_schema.character_data, identity_start information_schema.character_data, identity_increment information_schema.character_data, identity_maximum information_schema.character_data, identity_minimum information_schema.character_data, identity_cycle information_schema.yes_or_no, is_generated information_schema.character_data, generation_expression information_schema.character_data, is_updatable information_schema.yes_or_no);
            CREATE table {schema}.pg_attribute_start(dbname name, relation_size bigint, attrelid oid, attname name, atttypid oid, attstattarget	int4, attlen int2, attnum int2, attndims int4, attcacheoff int4, atttypmod int4, attbyval bool, attstorage char, attalign char, attnotnull bool, atthasdef bool, attisdropped bool, attislocal	bool, attinhcount int4, attcollation oid, attacl text[], attoptions text[], attfdwoptions text[]);
            CREATE table {schema}.pg_statistic_start(dbname name, starelid oid, staattnum int2, stainherit bool, stanullfrac float4, stawidth int4, stadistinct float4, stakind1 int2, stakind2 int2, stakind3 int2, stakind4 int2, stakind5 int2, staop1 oid, staop2 oid, staop3 oid, staop4 oid, staop5 oid, stanumbers1 float4[], stanumbers2 float4[], stanumbers3 float4[], stanumbers4 float4[], stanumbers5 float4[], stavalues1 text[], stavalues2 text[], stavalues3 text[], stavalues4 text[], stavalues5 text[]);
            end; """.format(schema =schema)


            create_end_databases_tables = """ begin;
            CREATE table {schema}.pg_stat_user_tables_end(dbname name, relid oid, schemaname name, relname name, seq_scan bigint, seq_tup_read bigint, idx_scan bigint, idx_tup_fetch bigint,n_tup_ins bigint, n_tup_upd bigint, n_tup_del bigint, n_tup_hot_upd bigint, n_live_tup bigint, n_dead_tup bigint, last_vacuum timestamp with time zone, last_autovacuum timestamp with time zone,last_analyze timestamp with time zone, last_autoanalyze timestamp with time zone, vacuum_count bigint, autovacuum_count bigint, analyze_count bigint, autoanalyze_count bigint);
            CREATE table {schema}.pg_stat_sys_tables_end(dbname name, relid oid, schemaname name, relname name, seq_scan bigint, seq_tup_read bigint, idx_scan bigint, idx_tup_fetch bigint,n_tup_ins bigint, n_tup_upd bigint, n_tup_del bigint, n_tup_hot_upd bigint, n_live_tup bigint, n_dead_tup bigint, last_vacuum timestamp with time zone, last_autovacuum timestamp with time zone,last_analyze timestamp with time zone, last_autoanalyze timestamp with time zone, vacuum_count bigint, autovacuum_count bigint, analyze_count bigint, autoanalyze_count bigint);
            CREATE table {schema}.pg_statio_user_tables_end(dbname name, relid oid, schemaname name, relname name, heap_blks_read bigint, heap_blks_hit bigint, idx_blks_read bigint, idx_blks_hit bigint, toast_blks_read bigint, toast_blks_hit bigint, tidx_blks_read bigint, tidx_blks_hit bigint);
            CREATE table {schema}.pg_statio_sys_tables_end(dbname name, relid oid, schemaname name, relname name, heap_blks_read bigint, heap_blks_hit bigint,  idx_blks_read bigint, idx_blks_hit bigint, toast_blks_read bigint, toast_blks_hit bigint, tidx_blks_read bigint, tidx_blks_hit bigint);
            CREATE table {schema}.pg_statio_user_indexes_end(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_blks_read bigint, idx_blks_hit bigint);
            CREATE table {schema}.pg_statio_sys_indexes_end(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_blks_read bigint, idx_blks_hit bigint);
            CREATE table {schema}.pg_stat_user_indexes_end(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint);
            CREATE table {schema}.pg_stat_sys_indexes_end(dbname name, relid oid, indexrelid oid, schemaname name, relname name , indexrelname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint);
            CREATE table {schema}.pg_stat_user_functions_end(dbname name, funcid oid, schemaname name, funcname name, calls bigint, total_time double precision, self_time double precision);
            CREATE table {schema}.other_stats_end(name TEXT, info TEXT);
            CREATE table {schema}.table_size_end(relid oid, schemaname name, relname name, pg_relation_size bigint, pg_total_relation_size bigint);
            end; """.format(schema =schema)

            log("Creating begining database collection tables.")
            executed = cur.execute(create_begin_databases_tables)
            if not executed == None:
                log("Database starting data did not load." + executed)
            log("Creating ending database collection tables.")
            executed = cur.execute(create_end_databases_tables)
            if not executed == None:
                log("Database ending data did not load." + executed)

    for num, dir in enumerate(os.walk(MONITOR_PATH_NAME)):
        if num == 0:
            continue
        full_path = dir[0]
        find = full_path.rfind('/') + 1
        print "Copying data for database " + full_path[find:]
        for file_name in dir[2]:
            begin = file_name.find( '.') + 1
            end = file_name[begin:].find('.')
            copy =""" COPY %s FROM '%s/%s' WITH DELIMITER ',' CSV HEADER """%(file_name[:(begin +end)],full_path,file_name)
            # try:
            cur.execute(copy)
            # except:
            #     schema_table_name = file_name[:(begin +end)]
            #     if 'pg_stat_sys_tables_start.csv' in file_name or 'pg_stat_user_tables_start.csv' in file_name or 'pg_stat_user_tables_end.csv' in file_name or 'pg_stat_sys_tables_end.csv' in file_name:
            #         try:
            #             alter = "alter table %s drop column vacuum_count restrict, drop column autovacuum_count restrict, drop column analyze_count restrict, drop column autoanalyze_count restrict;"%(schema_table_name)
            #             cur.execute(alter)
            #             cur.execute(copy)
            #         except:
            #             try:
            #                 drop_table = "DROP TABLE IF EXISTS %s CASCADE"%(schema_table_name)
            #                 create_table = "CREATE TABLE %s (dbname name, relid oid, schemaname name, relname name, seq_scan bigint, seq_tup_read bigint, idx_scan bigint, idx_tup_fetch bigint,n_tup_ins bigint, n_tup_upd bigint, n_tup_del bigint, n_tup_hot_upd bigint, n_live_tup bigint, n_dead_tup bigint, n_mod_since_analyze bigint, last_vacuum timestamp with time zone, last_autovacuum timestamp with time zone,last_analyze timestamp with time zone, last_autoanalyze timestamp with time zone, vacuum_count bigint, autovacuum_count bigint, analyze_count bigint, autoanalyze_count bigint);"%(schema_table_name)
            #                 cur.execute(drop_table)
            #                 cur.execute(create_table)
            #                 cur.execute(copy)
            #             except:
            #                 log("COPY failed on pg_stat_*_tables")
            #     elif 'pg_stat_activity' in file_name:
            #         drop_table = "DROP TABLE IF EXISTS %s CASCADE"%(schema_table_name)
            #         create_table = "CREATE table %s(datid oid, datname name, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp with time zone, xact_start timestamp with time zone, query_start timestamp with time zone, waiting boolean)"%(schema_table_name)
            #         cur.execute(drop_table)
            #         cur.execute(create_table)
            #         cur.execute(copy)
            #     elif 'pg_attribute_start' in file_name:
            #         alter = "alter table %s drop column attfdwoptions restrict;"%(schema_table_name)
            #         cur.execute(alter)
            #         try:
            #             cur.execute(copy)
            #         except:
            #             alter = "alter table %s drop column attcollation restrict;"%(schema_table_name)
            #             cur.execute(alter)
            #             cur.execute(copy)
            #
            #
            #     elif 'pg_statistic_start.csv' in file_name:
            #         with open(full_path + '/' + file_name, 'r') as file:
            #             line = file.readline()
            #         file.close()
            #         stakind = parse_pg_statistic_start(10, "stakind", line)
            #         new_stakind = ""
            #         for num in range(1,stakind+1):
            #             new_stakind = new_stakind + "stakind" + str(num)+" int2, "
            #
            #         staop = parse_pg_statistic_start(10, "staop", line)
            #         new_staop = ""
            #         for num in range(1,staop+1):
            #             new_staop = new_staop + "staop" + str(num)+" oid, "
            #
            #         stanumbers = parse_pg_statistic_start(10, "stanumbers", line)
            #         new_stanumbers = ""
            #         for num in range(1,stanumbers+1):
            #             new_stanumbers = new_stanumbers + "stanumbers" + str(num)+" float4[], "
            #
            #         stavalues = parse_pg_statistic_start(10, "stavalues", line)
            #         new_stavalues = ""
            #         for num in range(1,stavalues+1):
            #             new_stavalues = new_stavalues + "stavalues" + str(num)+" text[], "
            #         drop_table = "DROP TABLE IF EXISTS %s cascade"%(schema_table_name)
            #         create_table = "CREATE TABLE %s(dbname name, starelid oid, staattnum int2, stainherit bool, stanullfrac float4, stawidth int4, stadistinct float4, "%(schema_table_name)
            #         create_table = create_table + new_stakind + new_staop + new_stanumbers + new_stavalues[:-2] + ");"
            #         cur.execute(drop_table)
            #         cur.execute(create_table)
            #         cur.execute(copy)
            #     else:
            #         print "There was a problem with the copy: " + copy
        sleep(3)


