import random
from timeit import default_timer as timer
from py2neo import Graph
from neo4j.v1 import GraphDatabase, basic_auth
from statistics import mean

import psycopg2

noOfLoops = 3
threshold = 5
sourceId = -1
targetId = -1
####################################################################################3

PG_SHORTEST_PATH = "SELECT * FROM pgr_dijkstra('SELECT id, source, target, cost, reverse_cost " \
                   "FROM edge_table',{source}, {target})"

PG_COMMON_FRIENDS_ON_LEVEL = "WITH RECURSIVE transitive_closure(source, target, distance, path_string) " \
                          "AS( SELECT source, target, 1 AS distance, source || '.' || target || '.' " \
                          "AS path_string, target AS direct_connection FROM edge_table " \
                          "WHERE source= {source} " \
                          "" \
                          "UNION ALL " \
                          "" \
                          "SELECT tc.source, e.target, tc.distance + 1, tc.path_string || e.target || '.' " \
                          "AS path_string, tc.direct_connection FROM edge_table " \
                          "AS e JOIN transitive_closure AS tc ON e.source = tc.target " \
                          "WHERE tc.path_string NOT LIKE '%' || e.target || '.%' AND tc.distance < {level}) " \
                          "SELECT * FROM transitive_closure WHERE target={target} ORDER BY source,source,distance"



PG_FIND_CHIEF = "WITH RECURSIVE menu_tree(id_prac, url, level, id_szef) " \
             "AS ( SELECT id_prac, '' || id_prac, 0, id_szef " \
             "FROM nodesBuf  WHERE id_szef = 0 " \
             "UNION ALL " \
             "SELECT mn.id_prac, mt.url || '/' || mn.id_prac, mt.level + 1, mt.id_prac " \
             "FROM nodesBuf mn, menu_tree mt " \
             "WHERE mn.id_szef = mt.id_prac)" \
             "" \
             " SELECT * FROM menu_tree ORDER BY level, id_szef;"
PG_BIG_TEAMS = "SELECT a.id_prac, count(*) from nodesBuf a join nodesBuf b on a.id_prac = b.id_szef  " \
                    "GROUP BY (a.id_prac) HAVING count(*) > {big};"


####################################################################################3

NEO_SHORTEST_PATH = "MATCH (user1:User { id:1 }),(user2:User { id:1001 }), " \
                    "p = shortestPath((user1)-[*..15]-(user2)) RETURN p"

NEO_FIND_CHIEF = "match (n) where (n)-[:IS_FRIEND]->(n) return n"

NEO_BIG_TEAMS = "match (x)<-[]-(n) with x, count(n) as big where big > 40 return x,big order by big desc"

NEO_COMMON_FRIENDS = "MATCH (user1:User { id:1 }),(user2:User { id:1001 }), " \
                     "p = (user1)-[*..15]-(user2) " \
                     "UNWIND nodes(p) as common return distinct common"

##############################################################################3

GET_RANDOM_NODE = "SELECT id_prac FROM nodesBuf " \
                  "ORDER BY RANDOM() LIMIT 1"


##############################################################################3

def main():
    cursor, conn = connectToPgDb()
    driver = connectToNeoDb()

    getRandomNodes(cursor)

    start = "INSERT INTO EXPERIMENTS(id, instancesize, data, source, target)" \
            " VALUES(nextval('experimentId'), %s, now(), %s, %s)"
    cursor.execute(start, (instance_size, sourceId, targetId))

    SQLQuery(PG_SHORTEST_PATH, cursor, 'SHORTEST_PATH')
    SQLQuery(PG_COMMON_FRIENDS_ON_LEVEL, cursor, 'COMMON_FRIENDS_ON_LEVEL')
    SQLQuery(PG_FIND_CHIEF, cursor, 'FIND_CHIEF')
    SQLQuery(PG_BIG_TEAMS, cursor, 'BIG_TEAMS')

    NeoQuery(NEO_SHORTEST_PATH, driver, 'SHORTEST_PATH', cursor)
    NeoQuery(NEO_COMMON_FRIENDS, driver, 'COMMON_FRIENDS_ON_LEVEL', cursor)
    NeoQuery(NEO_FIND_CHIEF, driver, 'FIND_CHIEF', cursor)
    NeoQuery(NEO_BIG_TEAMS, driver, 'BIG_TEAMS', cursor)

    conn.commit()
    conn.close()


def SQLQuery(query, cursor, title):
    print('SQL ' + title)

    totalTime = 0
    for i in range(noOfLoops):
        print(i + 1, '/', noOfLoops, end=' | ')
        start = timer()

        cursor.execute(query.format(source = sourceId, target= targetId, size = instance_size,
                                    level=threshold, big = 40))

        records = cursor.fetchall()
        end = timer()
        totalTime += end - start

    print('\nResult : ', len(records), ' rows')

    final_result = totalTime / noOfLoops
    cursor.execute("Select max(id) from experiments")
    maxId = cursor.fetchall()

    insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
                "VALUES(%s, %s, %s, 'SQL')"
    cursor.execute(insertSql, (maxId[0][0], title, final_result,))



def NeoQuery(query, driver, title, cursor):
    print('NEO ' + title)

    totalTime = 0
    values = []
    for i in range(noOfLoops):
        print(i + 1, '/', noOfLoops, end=' | ')
        start = timer()
        session = driver.session()

        #print (query.format(source = sourceId, target= targetId, size = instance_size,level=threshold, big = 40))
        result = session.run(query).consume()
        values.append(result.result_consumed_after)
        session.close()

        end = timer()
        totalTime += end - start

    print('\nResult : ', len(values), ' rows')

    final_result = totalTime / noOfLoops
    cursor.execute("Select max(id) from experiments")
    maxId = cursor.fetchall()

    insertSql = "INSERT INTO EXPERIMENTINSTANCES(experimentid, exptype, duration, database_model) " \
                "VALUES(%s, %s, %s, 'NEO')"
    cursor.execute(insertSql, (maxId[0][0], title, final_result,))

def connectToPgDb():
    conn_string = "host='localhost' dbname='postgres' user='postgres' password='password'"
    # print the connection string we will use to connect
    print("Connecting to database")
    # get a connection, if a connect cannot be made an exception will be raised here
    conn = psycopg2.connect(conn_string)
    conn.commit()
    # conn.cursor will return a cursor object, you can use this cursor to perform queries
    cursor = conn.cursor()
    return cursor, conn

def connectToNeoDb():
    driver =  GraphDatabase.driver("bolt://localhost:7687", auth=basic_auth("neo4j", "guwno"))
    return driver;

def askForInstanceSize():
    selected_instance = input("Select size of instance:\n"
                              "x 6\n" \
                              "a 50\n" \
                              "b 10000\n" \
                              "c 100000\n" \
                              "d 500000\n\n" \
                              "Selected : ")
    choices = {'a': 50, 'b': 10000, 'c': 100000, 'd': 50000, 'x': 6}
    return choices.get(selected_instance, 'default')

def getRandomNodes(cursor):
    global sourceId, targetId

    sourceId = getRandomNode(cursor)

    targetId =getRandomNode(cursor)
    while sourceId == targetId:
        targetId = getRandomNode(cursor)
    print('Your random selected nodes are : sourceId ', sourceId, ' targetId ', targetId)

def getRandomNode(cursor):
    cursor.execute(GET_RANDOM_NODE.format(size = instance_size))
    tempResult = cursor.fetchall()
    result = tempResult[0][0]
    return result

if __name__ == "__main__":
    instance_size = askForInstanceSize()

    main()