import random
from MonetDB import *
from PostgreSQL import *
from datetime import datetime


#Bilioteques nécessaires (pour se connecter a monetDB et postgreeSQL
#pip install psycopg2
#pip install pymonetdb


#Monet DB
requeteSelect1 = """SELECT * FROM flights WHERE "AIRLINE" = 'AA';"""
requeteSelect2 = """SELECT * FROM flights;"""

requeteSELECT3 = """SELECT * FROM flights WHERE "YEAR" = 2015 AND "MONTH" = 12 AND "DAY" = 32 AND "DAY_OF_WEEK" = 4 AND "AIRLINE" = 'B6' AND "FLIGHT_NUMBER" = 839;"""

requeteSelect05 = """SELECT * FROM flights WHERE "DAY" > 5;"""
requeteSelect10 = """SELECT * FROM flights WHERE "DAY" > 10;"""
requeteSelect15 = """SELECT * FROM flights WHERE "DAY" > 15;"""
requeteSelect20 = """SELECT * FROM flights WHERE "DAY" > 20;"""
requeteSelect25 = """SELECT * FROM flights WHERE "DAY" > 25;"""
requeteSelect30 = """SELECT * FROM flights WHERE "DAY" > 30;"""

requeteSelect05_2 = """SELECT * FROM flights WHERE "DAY" > 5 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
requeteSelect10_2 = """SELECT * FROM flights WHERE "DAY" > 10 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
requeteSelect15_2 = """SELECT * FROM flights WHERE "DAY" > 15 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
requeteSelect20_2 = """SELECT * FROM flights WHERE "DAY" > 20 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
requeteSelect25_2 = """SELECT * FROM flights WHERE "DAY" > 25 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """
requeteSelect30_2 = """SELECT * FROM flights WHERE "DAY" > 30 AND "DAY_OF_WEEK"<=5 AND "SCHEDULED_DEPARTURE">1200; """

requeteSelect05_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 5;"""
requeteSelect10_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 10;"""
requeteSelect15_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 15;"""
requeteSelect20_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 20;"""
requeteSelect25_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 25;"""
requeteSelect30_3 = """SELECT "FLIGHT_NUMBER" FROM flights WHERE "DAY" > 30;"""

requeteCreateTable5 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 5;"""
requeteCreateTable10 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 10;"""
requeteCreateTable15 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 15;"""
requeteCreateTable20 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 20;"""
requeteCreateTable25 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 25;"""
requeteCreateTable30 = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS WHERE "DAY" > 30;"""

requeteDelete5 = """DELETE FROM flights WHERE "DAY" > 5;"""
requeteDelete10 = """DELETE FROM flights WHERE "DAY" > 10;"""
requeteDelete15 = """DELETE FROM flights WHERE "DAY" > 15;"""
requeteDelete20 = """DELETE FROM flights WHERE "DAY" > 20;"""
requeteDelete25 = """DELETE FROM flights WHERE "DAY" > 25;"""
requeteDelete30 = """DELETE FROM flights WHERE "DAY" > 30;"""

requeteInsert = """INSERT INTO flights SELECT * FROM flights2;"""

requeteCopyTable = """CREATE TABLE flights2 AS SELECT * FROM FLIGHTS;"""
requeteReplace = """UPDATE flights2 SET "WEATHER_DELAY" = -1 WHERE "WEATHER_DELAY" IS NULL;"""

requeteDropFlight2 = """DROP TABLE flights2;"""



rapport : str = ""

def initMonetDB():
    global rapport

    reset_data_monetdb()
    time = create_table_monetdb()
    result = "[MonetDB] create table: time = " + str(time) + " s \n"
    rapport = rapport + "\n" + result
    print(result)

    time = load_data_monetdb()
    result = "[MonetDB] load data : time = " + str(time) + " s \n"
    rapport = rapport + "\n" + result
    print(result)




def execMonetDB(str):
    return requete_monetdb(str)

def readMonetDB():
    execForMonetDB(5,[requeteSelect1])
    execForMonetDB(5,[requeteSelect2])

    execForMonetDB(5,[requeteSELECT3])

    execForMonetDB(5,[requeteSelect05])
    execForMonetDB(5,[requeteSelect10])
    execForMonetDB(5,[requeteSelect15])
    execForMonetDB(5,[requeteSelect20])
    execForMonetDB(5,[requeteSelect25])
    execForMonetDB(5,[requeteSelect30])

    execForMonetDB(5,[requeteSelect05_2])
    execForMonetDB(5,[requeteSelect10_2])
    execForMonetDB(5,[requeteSelect15_2])
    execForMonetDB(5,[requeteSelect20_2])
    execForMonetDB(5,[requeteSelect25_2])
    execForMonetDB(5,[requeteSelect30_2])

    execForMonetDB(10,[requeteSelect05_3])
    execForMonetDB(10,[requeteSelect10_3])
    execForMonetDB(10,[requeteSelect15_3])
    execForMonetDB(10,[requeteSelect20_3])
    execForMonetDB(10,[requeteSelect25_3])
    execForMonetDB(10,[requeteSelect30_3])
    
def readAndWriteMonetDB():
    execForMonetDB(4,[requeteCreateTable5,requeteDelete5,requeteInsert,requeteDropFlight2])
    execForMonetDB(4,[requeteCreateTable10,requeteDelete10,requeteInsert,requeteDropFlight2])
    execForMonetDB(4,[requeteCreateTable15,requeteDelete15,requeteInsert,requeteDropFlight2])
    execForMonetDB(4,[requeteCreateTable20,requeteDelete20,requeteInsert,requeteDropFlight2])
    execForMonetDB(4,[requeteCreateTable25,requeteDelete25,requeteInsert,requeteDropFlight2])
    execForMonetDB(4,[requeteCreateTable30,requeteDelete30,requeteInsert,requeteDropFlight2])
    

def randomWriteMonetDB(n):
    global rapport
    time = 0
    min = 0
    max = 0 
    for i in range(n):
        requete = """INSERT INTO flights ("YEAR","MONTH","DAY","DAY_OF_WEEK","AIRLINE","FLIGHT_NUMBER","TAIL_NUMBER","ORIGIN_AIRPORT","DESTINATION_AIRPORT","SCHEDULED_DEPARTURE","DEPARTURE_TIME") VALUES ("""
        requete += str(random.randint(2015,2016)) + "," + str(random.randint(1,12)) + "," + str(random.randint(1,31)) + "," + str(random.randint(1,7)) + ",'AA'," + str(random.randint(1,1000)) + ",'N1','JFK','LAX'," + str(random.randint(0,2400)) + "," + str(random.randint(0,2400)) + ");"
        time += execMonetDB(requete)[0]
        
        if(time<min or min == 0) :
            min = time
        if(time>max) :
            max = time
    time = time/n
    result = "[MonetDB] requete (moyenne de " + str(n) +") : INSERT INTO flights random value \n\ttime = " + str(time) + " s" + "\n\tmin time = " + str(min) + "\n\tmax time = " + str(max)
    rapport = rapport + "\n" + result
    print(result)


def writeMonetDB():
    randomWriteMonetDB(1000)




def runMonetDB():

    readMonetDB()

    readAndWriteMonetDB()

    writeMonetDB()



# execute n fois les n requêtes dans l'ordre
def execForMonetDB(n, tabReq):
    global rapport
    t = [[0] * len(tabReq) for _ in range(4)]
    for i in range(n) :
        print(i+1,"/",n)
        for j in range(len(tabReq)) :
            v = execMonetDB(tabReq[j])
            t[0][j] += v[0]
            t[1][j] = v[1]
            if(v[0]<t[2][j] or t[2][j] == 0) :
                t[2][j] = v[0]
            if(v[0]>t[3][j]) :
                t[3][j] = v[0]
            print("calcul en cours...")

    for j in range(len(tabReq)):
        t[0][j] = t[0][j]/n
        result = "[MonetDB] requete (moyenne de " + str(n) +") : " + str(tabReq[j]) + "\n\ttime = " + str(t[0][j]) + " s\n\tsize=" + str(t[1][j]) + "\n\tmin time = " + str(t[2][j]) + "\n\tmax time = " + str(t[3][j])
        rapport = rapport + "\n" + result
        print(result)



def initPostgreSQL():
    global rapport
    reset_database_postgres()
    time = create_tables_postgres()
    result = "[PostgreSQL] create table: time = " + str(time) + " s\n"
    rapport = rapport + "\n" + result
    print(result)

    time = create_data_postgres()
    result = "[PostgreSQL] load data : time = " + str(time) + " s\n"
    rapport = rapport + "\n" + result
    print(result)

def initIndex():
    global rapport
    time = execPostgreSQL("""CREATE INDEX idx_day ON flights ("DAY");""")
    result = "[PostgreSQL] create index : time = " + str(time) + " s\n"
    rapport = rapport + "\n" + result
    print(result)

def execPostgreSQL(str):
    return requete_postgres(str)

def readPostgreSQL():
    execForPostgreSQL(2,[requeteSelect1])
    execForPostgreSQL(2,[requeteSelect2])

    execForPostgreSQL(5,[requeteSELECT3])

    execForPostgreSQL(2,[requeteSelect05])
    execForPostgreSQL(2,[requeteSelect10])
    execForPostgreSQL(2,[requeteSelect15])
    execForPostgreSQL(2,[requeteSelect20])
    execForPostgreSQL(2,[requeteSelect25])
    execForPostgreSQL(3,[requeteSelect30])

    execForPostgreSQL(2,[requeteSelect05_2])
    execForPostgreSQL(2,[requeteSelect10_2])
    execForPostgreSQL(2,[requeteSelect15_2])
    execForPostgreSQL(2,[requeteSelect20_2])
    execForPostgreSQL(2,[requeteSelect25_2])
    execForPostgreSQL(2,[requeteSelect30_2])

    execForPostgreSQL(5,[requeteSelect05_3])
    execForPostgreSQL(5,[requeteSelect10_3])
    execForPostgreSQL(5,[requeteSelect15_3])
    execForPostgreSQL(5,[requeteSelect20_3])
    execForPostgreSQL(5,[requeteSelect25_3])
    execForPostgreSQL(5,[requeteSelect30_3])

def readAndWritePostgreSQL():
    execForPostgreSQL(4,[requeteCreateTable5,requeteDelete5,requeteInsert,requeteDropFlight2])
    execForPostgreSQL(4,[requeteCreateTable10,requeteDelete10,requeteInsert,requeteDropFlight2])
    execForPostgreSQL(4,[requeteCreateTable15,requeteDelete15,requeteInsert,requeteDropFlight2])
    execForPostgreSQL(4,[requeteCreateTable20,requeteDelete20,requeteInsert,requeteDropFlight2])
    execForPostgreSQL(4,[requeteCreateTable25,requeteDelete25,requeteInsert,requeteDropFlight2])
    execForPostgreSQL(4,[requeteCreateTable30,requeteDelete30,requeteInsert,requeteDropFlight2])


def randomWritePostgreSQL(n):
    global rapport
    time = 0
    min = 0
    max = 0
    for i in range(n):
        requete = """INSERT INTO flights ("YEAR","MONTH","DAY","DAY_OF_WEEK","AIRLINE","FLIGHT_NUMBER","TAIL_NUMBER","ORIGIN_AIRPORT","DESTINATION_AIRPORT","SCHEDULED_DEPARTURE","DEPARTURE_TIME") VALUES ("""
        requete += str(random.randint(2015,2016)) + "," + str(random.randint(1,12)) + "," + str(random.randint(1,31)) + "," + str(random.randint(1,7)) + ",'AA'," + str(random.randint(1,1000)) + ",'N1','JFK','LAX'," + str(random.randint(0,2400)) + "," + str(random.randint(0,2400)) + ");"
        time += execPostgreSQL(requete)[0]
        if(time<min or min == 0) :
            min = time
        if(time>max) :
            max = time
    time = time/n
    result = "[PostgreSQL] requete (moyenne de " + str(n) +") : INSERT INTO flights random value \n\ttime = " + str(time) + " s" + "\n\tmin time = " + str(min) + "\n\tmax time = " + str(max)
    rapport = rapport + "\n" + result
    print(result)

def writePostgreSQL():
    randomWritePostgreSQL(1000)




def runPostgreSQL():

    readPostgreSQL()

    readAndWritePostgreSQL()

    writePostgreSQL()


# execute n fois les n requêtes dans l'ordre
def execForPostgreSQL(n, tabReq):
    global rapport
    t = [[0] * len(tabReq) for _ in range(4)]
    # avg nbResult min max
    for i in range(n):
        print(i+1,"/",n)
        for j in range(len(tabReq)):
            v = execPostgreSQL(tabReq[j])
            t[0][j] += v[0]
            t[1][j] = v[1]
            if(v[0]<t[2][j] or t[2][j] == 0) :
                t[2][j] = v[0]
            if(v[0]>t[3][j]) :
                t[3][j] = v[0]
            print("calcul en cours...")

    for j in range(len(tabReq)):
        t[0][j] = t[0][j] / n
        result = "[PostgreSQL] requete (moyenne de " + str(n) +") : " + str(tabReq[j]) + "\n\ttime = " + str(t[0][j]) + " s\n\tsize=" + str(t[1][j]) + "\n\tmin time = " + str(t[2][j]) + "\n\tmax time = " + str(t[3][j])
        rapport = rapport + "\n" + result
        print(result)

if __name__ == '__main__':

    initMonetDB()
    runMonetDB()

    rapport += "\n"
    
    initPostgreSQL()
    #initIndex()
    runPostgreSQL()

    print("/////////////////////////////////////////////////////////////")
    print(rapport)
    print("/////////////////////////////////////////////////////////////")

    date = datetime.now()
    nom_fichier = "result" + date.strftime("_%d_%m_%y_%H_%M_%S") + ".txt" #un nom de fichier unique pour ne pas écraser les anciens résultats
    with open(nom_fichier, 'w') as fichier:
        fichier.write(rapport)
#