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

path = str()
if len(sys.argv) > 1:
  path = sys.argv[1]
else:
  print("Invalid command line arguments.")
  exit(1)

print(path)

with open(path, newline='') as csvfile:
  csvreader = csv.reader(csvfile, delimiter=',') # , quotechar='|'
  next(csvreader) # skip the first line (table names)
  sequences = set()
  toolLogChanges = []
  for row in csvreader:
    sequence = row[0]
    subject = row[1]
    logExpr = row[2]
    startPos = row[3]
    oldLevel = row[4]
    fqn = row[5]
    enclosingMethod = row[6]
    doi = row[7]
    action = row[8]
    newLevel = row[9]
    sequences.add(sequence)
    # sequence, raw_subject, old_level, new_level, log_expr, fqn, start_pos, action, doi_val, enclosing_method
    toolLogChanges.append((sequence, subject, oldLevel, newLevel, logExpr, fqn, startPos, action, doi, enclosingMethod))

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()
    print("Deleting duplicates")
    for sequence in sequences:
      try:
       # print(sequence)
        cursor.execute("SET FOREIGN_KEY_CHECKS=0")
        query = "DELETE FROM logging.tool_log_changes WHERE sequence = \"{}\"".format(sequence)
        cursor.execute(query)
        if cursor.rowcount > 0 :
          print("Deleting duplicate sequence {} amount {}".format(sequence,cursor.rowcount))
        cursor.execute("SET FOREIGN_KEY_CHECKS=1")
      except mysql.connector.Error as err:
        if err.errno != errorcode.ER_DUP_ENTRY:
          print(err)
          print(subject)

    print("Importing tool log changes")
    for row in toolLogChanges:
      try:
        query = "SELECT subject from logging.working_sets WHERE subject_raw = '{}'".format(row[1])
        cursor.execute(query)
        sub = cursor.fetchone()
        if sub is not None:
          query = """INSERT INTO logging.tool_log_changes
                  (sequence, raw_subject, old_level, new_level, log_expr, fqn, start_pos, action, doi_val, enclosing_method)
                    VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"""
          cursor.execute(query,(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9]))
        else:
          print("[Error Inserting] No subject exists for raw subject {}".format(row[1]))
      except mysql.connector.Error as err:
        if err.errno != errorcode.ER_DUP_ENTRY:
          print(err)
          print(row)

    cursor.close()
    print("Commiting changes to DB")
    cnx.commit()
    print("Closing connection")
    cnx.close()
    print("done")
