# This places the tables in the database and sets up indexes.
# To prepare the data, first replace tabs by commas.
# Next, for the singles file vikramonebody.csv, change the header from
# [BEGIN ONEBODY SEQPOS/ROTINDEX/ENERGY]
# to
# position,rotamer,energy
# For the pairs file vikramtwobody.csv, change the header from
#  whatever it is to
# firstposition,firstrotamer,secondposition,secondrotamer,energy
# Within the current file after the read, all positions and rotamers will
# be reduced by 1 (e.g. position i becomes i-1 and rotamer r becomes r-1)
# so everything can be zero-based. The final bestpath output has to be adjusted
# to the correct rotamer



import sys
import math
import csv
import os
import copy
import operator
import doctest
import collections
import datetime
import random
from operator import itemgetter, attrgetter
sys.setrecursionlimit(20000) # ??? might have to extend for different cities

import pandas as pd
import numpy as np
import sqlite3

now = datetime.datetime.now()
currentyear = now.year

#sqlite
connection = sqlite3.connect("aquarium.db")
print(connection.total_changes)
cursor = connection.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS  singleenergy(position INTEGER, rotamer INTEGER, energy FLOAT)")

cursor.execute("CREATE TABLE IF NOT EXISTS  crossenergy(firstposition INTEGER, firstrotamer INTEGER, secondposition INTEGER, secondrotamer INTEGER,  energy FLOAT)")

# df = pd.read_csv("singles.csv")
df = pd.read_csv("vikramonebody.csv")
df.columns = df.columns.str.strip()
df.to_sql('singleenergy',connection,if_exists='replace')

# cursor.execute("UPDATE singleenergy SET position = CAST(position as INTEGER), rotamer = CAST(rotamer as INTEGER)")

# Update needed for data coming from Vikram 
# because the positions and rotamers are
# not zero-based, but we want them to be
cursor.execute("UPDATE singleenergy SET position = position-1, rotamer = rotamer-1")


cursor.execute("CREATE INDEX single_position ON singleenergy(position)")
rows = cursor.execute("SELECT position, rotamer, energy FROM singleenergy where position = 0").fetchall()
print(rows)


# df = pd.read_csv("pairs.csv")
df = pd.read_csv("vikramtwobody.csv")

df.columns = df.columns.str.strip()
df.to_sql('crossenergy',connection,if_exists='replace')


# cursor.execute("UPDATE crossenergy SET firstposition = CAST(firstposition as INTEGER), firstrotamer = CAST(firstrotamer as INTEGER), secondposition = CAST(secondposition as INTEGER), secondrotamer = CAST(secondrotamer as INTEGER)")


# Update needed for data coming from Vikram 
# because the positions and rotamers are
# not zero-based, but we want them to be
cursor.execute("UPDATE crossenergy SET firstposition = firstposition-1, firstrotamer = firstrotamer-1, secondposition = secondposition-1, secondrotamer = secondrotamer-1")
cursor.execute("CREATE INDEX pairs_positions ON crossenergy(firstposition, secondposition)")
cursor.execute("CREATE INDEX pairs_posrot ON crossenergy(firstposition, firstrotamer)")

connection.close()