import mysql.connector
import pandas as pd
import os

debr_databases = ["debriefing_left", "debriefing_right"]

print("Connect to database")
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="tangram",
  database="experiment",
  port = 3306
)

print("truncate tables")
mycursor = mydb.cursor()
mycursor.execute("TRUNCATE TABLE silhouette")
mycursor.execute("TRUNCATE TABLE debriefing_left")
mycursor.execute("TRUNCATE TABLE debriefing_right")
mycursor.execute("TRUNCATE TABLE minesweeper_silhouette")
mycursor.execute("TRUNCATE TABLE association_learning_trials")
mycursor.execute("TRUNCATE TABLE navigation_trials")

mydb.commit()


for d in [f for f in os.listdir("Stims") if os.path.isdir(os.path.join("Stims", f))]:
  path = os.path.join("Stims", d)
  for num in [f for f in os.listdir(path) if os.path.isdir(os.path.join(path,f))]:
    stimset = d+"_"+num
    stimset = stimset.replace("_", "")
    print("\n\n\n ### Stimset: ", stimset, " ###\n")
    print("Read csv files")

    if len([f for f in os.listdir(os.path.join(path,num)) if f.endswith(".csv")]) ==0:
      print("skip")
      continue

    silhouettes = pd.read_csv(os.path.join(path,num,"Block_coordinates.csv"), header=None, sep="\n")
    trial_types = pd.read_csv(os.path.join(path,num,"Trialtype.csv"), sep="\n")
    # debr_left = pd.read_csv(os.path.join(path,num,"Block_coordinates_debrief_left.csv"), header=None, sep="\n")
    # debr_right = pd.read_csv(os.path.join(path,num,"Block_coordinates_debrief_right.csv"), header=None, sep="\n")
    # debr_csvs = [debr_left,debr_right]
    # debr_trialtypes = pd.read_csv(os.path.join(path,num,"debrief_trial.csv"), sep="\n")
    # minesweeper_silhouettes = pd.read_csv(os.path.join(path,num,"Block_coordinates_minesweeper.csv"), header=None, sep="\n")
    # minesweeper_solutions = pd.read_csv(os.path.join(path,num,"Block_coordinates_minesweeper_occl.csv"), header=None, sep="\n")


    # if exists
    # minesweeper_trialtype = None
    # has_minesweeper_trial_type = False
    # if os.path.isfile(os.path.join(path,num,"Stim_trial_minesweeper.csv")):
    #   minesweeper_trialtype = pd.read_csv(os.path.join(path,num,"Stim_trial_minesweeper.csv"), sep="\n")
    #   has_minesweeper_trial_type = True

    ### Silhouettes ###
    print("\nSilhouettes")
    val = []
    current_trial = 0
    coordinate_string = "["
    for i, solution in enumerate(silhouettes[0][1:]):
        array = solution.split(",")
        x =  str(int(array[0]))
        y = str(int(array[1]))
        id = array [2]
        trial = int(array[3])

        if trial != current_trial:
          coordinate_string = coordinate_string[:-1] + "]"
          val.append((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset))
          #print((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset))
          coordinate_string = "["
          current_trial = trial
        coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"},"

    # last entry
    coordinate_string = coordinate_string[:-1] + "]"
    val.append((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset))
    #print((current_trial,coordinate_string,str(trial_types["Trialtype"][current_trial]),stimset))

    print("Insert ",len(val), "solutions")
    sql = "INSERT INTO silhouette (silhouette_id,solution,trial_type, stimset) VALUES (%s, %s, %s, %s)"
    mycursor.executemany(sql, val)
    mydb.commit()
#
#
#     ### Debriefing ###
#     print("\nDebriefing")
#     for idx, csv in enumerate(debr_csvs):
#       val = []
#       current_trial = 0
#       coordinate_string = "["
#       for i, solution in enumerate(csv[0][1:]):
#           array = solution.split(",")
#           x =  str(int(float(array[0])))
#           y = str(int(float(array[1])))
#           id = str(int(float(array[2])))
#           trial = int(float(array[3]))
#
#           if x=="0" and y=="0":
#             continue
#
#           if trial != current_trial:
#             coordinate_string = coordinate_string[:-1] + "]"
#             type = debr_trialtypes["Stim Type Left,Stim Type Right"][current_trial].split(",")[idx]
#             val.append((current_trial,coordinate_string,stimset,type))
#             #print((current_trial,coordinate_string,stimset,type))
#             coordinate_string = "["
#             current_trial = trial
#
#           coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"},"
#
#       # last entry
#       coordinate_string = coordinate_string[:-1] + "]"
#       val.append((current_trial,coordinate_string,stimset,type))
#       #print((current_trial,coordinate_string,stimset,type))
#
#       print("Insert ",len(val), "solutions to ", debr_databases[idx])
#       sql = "INSERT INTO "+ debr_databases[idx] +" (debriefing_id,solution,stimset,trial_type) VALUES (%s, %s, %s, %s)"
#       mycursor.executemany(sql, val)
#       mydb.commit()
#
#
#
#     ### Minesweeper ###
#     print("\nMinesweeper")
#     val = []
#     current_trial = 0
#     coordinate_string = "["
#     for i, silhouette in enumerate(minesweeper_silhouettes[0][1:]):
#       array = silhouette.split(",")
#       x =  str(int(array[0]))
#       y = str(int(array[1]))
#       id = array [2]
#       trial = int(array[3])
#
#       if trial != current_trial:
#         type = 0
#         if has_minesweeper_trial_type:
#           type = str(minesweeper_trialtype["StimID"][current_trial])
#           #print(type)
#         coordinate_string = coordinate_string[:-1] + "]"
#         solution = minesweeper_solutions.iloc[trial][0].split(",")
#         solution_str = "{\"x\":"+solution[0]+", \"y\":"+solution[1]+", \"id\":"+solution[2]+"}"
#         val.append((current_trial,coordinate_string, solution_str, stimset,type))
#         #print(val[trial-1])
#         coordinate_string = "["
#         current_trial = trial
#       coordinate_string+="{\"x\":"+x+", \"y\":"+y+", \"id\":"+id+"},"
#
#     # last entry
#     solution = minesweeper_solutions.iloc[trial][0].split(",")
#     solution_str = "{\"x\":"+solution[0]+", \"y\":"+solution[1]+", \"id\":"+solution[2]+"}"
#     coordinate_string = coordinate_string[:-1] + "]"
#     val.append((current_trial,coordinate_string, solution_str, stimset, type))
#     #print(val[trial])
#
#     print("Insert ",len(val), "solutions")
#     sql = "INSERT INTO minesweeper_silhouette (minesweeper_silhouette_id,silhouette,solution,stimset,trialtype) VALUES (%s, %s, %s, %s, %s)"
#     mycursor.executemany(sql, val)
#     mydb.commit()
#
#
# # Association Learning Trials
# print("\n\n\n ### Association Data ###\n")
# path = os.path.join("Stims", "Association")
# association_trials = pd.read_csv(os.path.join(path,"Trials_AssocLearning.csv"), header=None, sep="\n")
# val = []
# for i,record in enumerate(association_trials[0][1:]):
#   stim_ID, stim_type, switch, target_id, target_type = record.split(",")
#   val.append((stim_ID, stim_type, switch, target_id, target_type))
#
# print("Insert ",len(val), "solutions to association_learning_trials")
# sql = "INSERT INTO association_learning_trials (stim_id, stim_type, switch, target_id, target_type) VALUES (%s, %s, %s, %s, %s)"
# mycursor.executemany(sql, val)
# mydb.commit()
#
#
#
# # Association Navigation Task
# path = os.path.join("Stims", "Association")
# navigation_trials = pd.read_csv(os.path.join(path,"Trials_Navigation.csv"), header=None, sep="\n")
# val = []
# for i,record in enumerate(navigation_trials[0][1:]):
#   solution, left, right, show_length = record.split(",")
#   if(show_length =="True"):
#     show_length = 1
#   else:
#     show_length = 0
#   val.append((solution, left, right, show_length))
#
# print("Insert ",len(val), "solutions to navigation_trials")
# sql = "INSERT INTO navigation_trials (solution, stim_left, stim_right, show_length) VALUES (%s, %s, %s, %s)"
# mycursor.executemany(sql, val)
# mydb.commit()

print("\n\n\nDone")