import mysql.connector
from mysql.connector import errorcode
import sys
import csv
import re
import datetime

LEVEL_TO_INT = {
        "SEVERE": 2,
        "WARNING": 1,
        "INFO": 0,
        "CONFIG": 0,
        "FINE": -1,
        "FINER": -2,
        "FINEST": -3}

def distance(toolLevel,oracleLevel):
    dist = abs(LEVEL_TO_INT[toolLevel] - LEVEL_TO_INT[oracleLevel])
    return dist, dist / (len(LEVEL_TO_INT) - 1) * 100

if __name__ == "__main__":
    sequence = None

    if len(sys.argv) > 2:
      sequence = sys.argv[1]
      # YYYY-MM-DD
      fDate = datetime.datetime.strptime(sys.argv[2], "%Y-%m-%d").strftime("%Y-%m-%d")
    else:
      print("Proper command line arguments not supplied.")
      sys.exit(1)

    try:
      cnx = mysql.connector.connect(user='allan', password='3QT2wZ4yeXcACr2X',
                                  host='127.0.0.1',
                                  database='logging',
                                  port ='3307')

    except mysql.connector.Error as err:
      if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
      elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
      else:
        print(err)
    else:
        print("Connected to DB")
        cursor = cnx.cursor()

        # (subject, [tool changes])
        oracle = {}
        print("Loading Oracle Data")
        # name, url, sha1_head
        # id, repo_name, sha1_change, original_expr, relative_file_name, old_level, new_level
        try:
          query = "SELECT * FROM logging.oracle_log_changes"
          cursor.execute(query)
          for row in cursor.fetchall():
            id = row[0]
            repo_name = row[1]
            sha1_change = row[2]
            original_expr = row[3]
            relative_file_name = row[4]
            old_level = row[5]
            new_level = row[6]
            date = datetime.datetime.strptime(str(row[7]), '%Y-%m-%d %H:%M:%S').strftime("%Y-%m-%d") # row[7]
            oracle.setdefault(repo_name,[]).append([id, sha1_change, original_expr, relative_file_name, old_level, new_level, date])
        except mysql.connector.Error as err:
          print(err)
          print(query)

        print("Loading Tool Data")
        tool = {}
        # id, sequence, raw_subject, old_level, new_level, log_expr, fqn, start_pos, action, doi_val, enclosing_method, subject_raw, subject
        try:
          query = "SELECT * FROM logging.tool_log_changes lc INNER JOIN (SELECT subject_raw, subject FROM logging.working_sets) ws ON lc.raw_subject = ws.subject_raw WHERE sequence = \"{}\" ORDER BY ws.subject_raw".format(sequence)
          cursor.execute(query)
          for row in cursor.fetchall():
            # print(row)
            id = row[0]
            sequence = row[1]
            subject = row[12]
            old_level = row[3]
            new_level = row[4]
            log_expr = row[5]
            fqn = row[6]
            tool.setdefault(subject,[]).append([id, log_expr, fqn, old_level, new_level])
        except mysql.connector.Error as err:
          print(err)
          print(query)

        #for every tool output, search for a match in oracle
        # TP FP FN (do we care about false negatives?)
        try:
          TPHandle = open("TP.csv", 'a+', newline='')
          TPWriter = csv.writer(TPHandle, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
          FPHandle = open("FP.csv", 'a+', newline='')
          FPWriter = csv.writer(FPHandle, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
          FNHandle = open("FN.csv", 'a+', newline='')
          FNWriter = csv.writer(FNHandle, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)

          TPWriter.writerow(["Sequence","Subject","TID","OID","TExpr","OExpr","File path","FQN","Before","TAfter","OAfter","Distance","Distance ratio"])
          FPWriter.writerow(["Sequence","Subject","TID","TExpr","FQN","Before","After"])
          FNWriter.writerow(["Sequence","Subject","OID","OExpr","File path","Before","After"])

        except IOError as ec:
          print(ec)
          sys.exit(1)

        print("Finding matches")
        for subject, toolRows in tool.items():
          for toolRow in toolRows:
            convertedFQN = re.sub('\.','/',toolRow[2]) # replace . with / in fqn

            # Remove embedded type signatures.
            dollarSignPos = convertedFQN.find('$')

            if dollarSignPos != -1:
                # It's an embedded type. Remove it.
                convertedFQN = convertedFQN[:dollarSignPos]

            isMatch = False
            if subject in oracle:
              rowsToRemove = list()
              for oracleRow in oracle[subject]:
                if oracleRow[6] < fDate: # filter by date here
                  print("filtered by date : {} < {}".format(oracleRow[6],fDate))
                  rowsToRemove.append(oracleRow)
                elif re.search(convertedFQN, oracleRow[3]): # match fqn + fPath
                  if toolRow[3] == oracleRow[4]: # and toolRow[4] == oracleRow[5]
                    # re.sub('[ \t\n"\';]','',toolString)
                    tTemp = re.sub('[ \t\n"\'\(\)\[\];,]','',toolRow[1])
                    oTemp = re.sub('[ \t\n"\'\(\)\[\];,]','',oracleRow[2])
                    if tTemp == oTemp: # match log expression tTemp == oTemp # re.search(oTemp, tTemp):
                      isMatch = True
                      rowsToRemove.append(oracleRow)
                      ldistance, distRatio = distance(toolRow[4],oracleRow[5])
                      # TPWriter.writerow(["Sequence","Subject","TID","OID","TExpr","OExpr","File path","FQN","Before","TAfter","OAfter","Distance","Distance ratio"])
                      TPWriter.writerow((sequence,subject,toolRow[0],oracleRow[0],toolRow[1],oracleRow[2],oracleRow[3],toolRow[2],toolRow[3], \
                              toolRow[4],oracleRow[5],ldistance,distRatio))
              for row in rowsToRemove:
                # print("Removing matched or filtered rows : {}".format(row))
                oracle[subject].remove(row)
            else:
              print("Oracle does not contain the subject : " + subject + " or your date is invalid")

            if not isMatch: # no match found
              # FPWriter.writerow(["Sequence","Subject","TID","TExpr",","FQN","Before","After"])
              FPWriter.writerow([sequence,subject,toolRow[0],toolRow[1],toolRow[2],toolRow[3],toolRow[4]])

        print("Dumping all unmatched into FN file")
        for subject, rows in oracle.items():
          for row in rows:
            if subject in tool:
              # ["Sequence","Subject","OID","OExpr","File path","Before","After"])
              FNWriter.writerow([sequence,subject,row[0],row[2],row[3],row[4],row[5]])

        cursor.close()
        print("Closing connection")
        cnx.close()
        TPHandle.close()
        FPHandle.close()
        FNHandle.close()
        print("done")
