#!/usr/bin/env python
import mysql.connector
import sqlite3 as lite
import sys

mydb = mysql.connector.connect(
  host="hallddb",
  user="tofuser",
  passwd="",
  database="TOFMapTemp"
)

mycursor = mydb.cursor()

# Connect to SQLite DB file
db_filename = sys.argv[1]
print ""
print "opening SQLite DB file: %s" % db_filename
print "---------------------------------------------"
con = lite.connect(db_filename)
con.row_factory = lite.Row
cur = con.cursor()
# get the index of the run range
cur.execute("SELECT runrangeid FROM RunRange WHERE name = \"RunPeriod-2019-11-default\"")
row = cur.fetchone()
runRangeId = str(row[0])

theQuery = """select
splitterLocation.id, splitterLocation.label,
signalCable.label,
pmtLocation.end, pmtLocation.label, pmtLocation.labelPhysics,
moduleLocation.layer, moduleLocation.bar,
adcCable.label, adcCable.channel,
adcLocation.crateLocationId, adcLocation.slot,
discCable.label, discCable.channel,
discLocation.crateLocationId, discLocation.slot,
tdcCable.label, tdcCable.discChannel, tdcCable.tdcHalf, tdcCable.tdcChannel,
tdcLocation.crateLocationId, tdcLocation.slot
from
splitterLocation, signalCable, pmtLocation, moduleLocation, adcCable,
adcLocation, discCable, discLocation, tdcCable, tdcLocation
where
signalCable.splitterLocationId = splitterLocation.id
and pmtLocationId = pmtLocation.id
and moduleLocationId = moduleLocation.id
and adcCable.splitterLocationId = splitterLocation.id
and adcLocationId = adcLocation.id
and discCable.splitterLocationId = splitterLocation.id
and discCable.discLocationId = discLocation.id
and tdcCable.discLocationId = discLocation.id
and tdcLocationId = tdcLocation.id
and tdcCable.label = discCable.label
order by splitterLocation.id;"""

mycursor.execute(theQuery)

myresult = mycursor.fetchall()

for x in myresult:
  myend = x[3]
  layer = x[6]
  if layer == -1:
    plane = '1'
    if myend == -1:
      end = 'S'
    elif myend == 1:
      end = 'N'
    else:
      exit(1)
  elif layer == 1:
    plane = '0'
    if myend == -1:
      end = 'DW'
    elif myend == 1:
      end = 'UP'
    else:
      exit(1)
  else:
    exit(1)
  barInt = x[7]
  barString = str(barInt)
  adcLabel = str(x[8])
  tdcLabel = str(x[16])
  discLabel = str(x[12])
  if barInt == 23 or barInt == 24:
# add the adc channel
    cur.execute("SELECT MAX(chanid) FROM Channel")
    row = cur.fetchone()
    chanid = row[0] + 1
    adcslot = str(x[11])
    adcModQuery = "SELECT moduleid FROM Module, Crate WHERE Crate.name = \"D2-1-TOP\" and slot = " + adcslot + " and Module.crateid = Crate.crateid;"
    cur.execute(adcModQuery)
    row = cur.fetchone()
    adc_moduleid = str(row[0])
    channel = str(x[9])
    insertQuery = "INSERT INTO Channel (chanid, moduleid, name, channel, system,col_name, enable) VALUES (" + str(chanid) + ", " + adc_moduleid + ", \"" + adcLabel + "\", " + channel + ", \"TOF\", \"adc_chanid\", " + runRangeId + ")"
    print(insertQuery)
    cur.execute(insertQuery)
    con.commit()
# add the tdc channel
    cur.execute("SELECT MAX(chanid) FROM Channel")
    row = cur.fetchone()
    chanid = row[0] + 1
    tdcslot = str(x[21])
    tdcModQuery = "SELECT moduleid FROM Module, Crate WHERE Crate.name = \"D2-1-MID\" and slot = " + tdcslot + " and Module.crateid = Crate.crateid;"
    cur.execute(tdcModQuery)
    row = cur.fetchone()
    tdc_moduleid = str(row[0])
    channel = str(x[19])
    insertQuery = "INSERT INTO Channel (chanid, moduleid, name, channel, system,col_name, enable) VALUES (" + str(chanid) + ", " + tdc_moduleid + ", \"" + tdcLabel + "\", " + channel + ", \"TOF\", \"tdc_chanid\", " + runRangeId + ")"
    print(insertQuery)
    cur.execute(insertQuery)
    con.commit()
# add the discriminator channel
    cur.execute("SELECT MAX(chanid) FROM Channel")
    row = cur.fetchone()
    chanid = row[0] + 1
    discslot = str(x[15])
    discModQuery = "SELECT moduleid FROM Module, Crate WHERE Crate.name = \"D2-1-BOT\" and slot = " + discslot + " and Module.crateid = Crate.crateid;"
    cur.execute(discModQuery)
    row = cur.fetchone()
    disc_moduleid = str(row[0])
    channel = str(x[13])
    insertQuery = "INSERT INTO Channel (chanid, moduleid, name, channel, system,col_name, enable) VALUES (" + str(chanid) + ", " + disc_moduleid + ", \"" + discLabel + "\", " + channel + ", \"TOF\", \"disc_chanid\", " + runRangeId + ")"
    print(insertQuery)
    cur.execute(insertQuery)
    con.commit()
  if barInt >= 23:
    adcQuery = "SELECT chanid FROM Channel WHERE name = \"" + adcLabel + "\" AND col_name = \"adc_chanid\";"
    cur.execute(adcQuery)
    rowa = cur.fetchone()
    adc_chanid = str(rowa[0])
    tdcQuery = "SELECT chanid FROM Channel WHERE name = \"" + tdcLabel + "\" AND col_name = \"tdc_chanid\";"
    cur.execute(tdcQuery)
    rowt = cur.fetchone()
    tdc_chanid = str(rowt[0])
    discQuery = "SELECT chanid FROM Channel WHERE name = \"" + discLabel + "\" AND col_name = \"disc_chanid\";"
    cur.execute(discQuery)
    rowd = cur.fetchone()
    disc_chanid = str(rowd[0])
    tofInsertQuery = "INSERT INTO TOF (plane, bar, end, adc_chanid, tdc_chanid, disc_chanid, runrangeid) VALUES (" + plane + ", " + barString + ", \"" + end + "\", " + adc_chanid + ", " + tdc_chanid + ", " + disc_chanid + ", " + runRangeId + ");"
    print(tofInsertQuery)
    cur.execute(tofInsertQuery)
    con.commit()

exit(0)