#!/usr/bin/python

# This script fixes an issue with the numbering of the TAGM channels
# due to an older version of the tt_csv2db.py script being used 
# by the controls group that did not include a fix committed 8/14/2014.
# It will drop the TAGM table and regenerate it followed by an update
# of the Channel table. This is done regardless whether the DB needs
# the update.


import sqlite3 as lite
import sys
import os
import getpass
import socket
import itertools
import csv
import re

from time import gmtime, localtime, strftime

if len(sys.argv) != 2:
	print "You must supply a sqlite file (e.g. tt.db)"
	sys.exit(0);

con = lite.connect(sys.argv[1])

with con:

	# Specify that next cursor should be "dictionary"
	# (i.e. python's hash map) so columns can be indexed
	# by name
	con.row_factory = lite.Row

	# Create Cursor
	cur = con.cursor()
	
	# The following was copied from tt_csv2db.py. This is from the current
	# version of the script. It is easiest to just drop the existing TAGM
	# table and refill it.
	print "Dropping TAGM ...."
	cur.execute("DROP TABLE TAGM")
	

	#=====================================================================
	# TAGM
	#
	# The tagger microscope is outfitted for readout such that 95 of the
	# 100 columns have the 5 SiPMs summed in hardware so that there is a
	# single fADC channel for them. The other 5 columns (evenly spaced
	# throughout the microscope region) have all 5 SiPMs read out
	# individually so that they can be used to help tune the beam to be
	# centered along the plane defined by the central fibers. This makes
	# for 95 + (5*5) = 120 TAGM readout channels total.
	#=====================================================================
	print "Linking TAGM ...."
	cur.execute("CREATE TABLE TAGM(col INT, row INT, adc_chanid INTEGER, tdc_chanid INTEGER, disc_chanid INTEGER)")
	daq_chan = 1
	for col in range (1,101):
		max_row = 1
		if ((col-1)%20)==0: max_row=5
		for row in range (1,max_row+1):
			(adc_chanid, tdc_chanid, disc_chanid) = (-1, -1, -1)
			try:
				cur.execute("SELECT chanid FROM Channel WHERE name='TAGM-A-%d'" % (daq_chan))
				(adc_chanid,) = cur.fetchone()
				cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGM-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%TDC%%'" % (daq_chan))
				(tdc_chanid,) = cur.fetchone()
				cur.execute("SELECT chanid FROM Channel,Module WHERE name='TAGM-T-%d' AND Channel.moduleid=Module.moduleid AND type LIKE '%%DISC%%'" % (daq_chan))
				(disc_chanid,) = cur.fetchone()
			except:
				pass # Do nothing here

			if int(adc_chanid)>=0:
				cur.execute("INSERT INTO TAGM VALUES(%d,%d,%d,%d,%d)" %(col, row, adc_chanid, tdc_chanid, disc_chanid))
				daq_chan += 1

	cur.execute("SELECT count(*) FROM TAGM WHERE adc_chanid>=0") ; (Ntagm_adc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGM WHERE tdc_chanid>=0") ; (Ntagm_tdc,) = cur.fetchone()
	cur.execute("SELECT count(*) FROM TAGM WHERE disc_chanid>=0") ; (Ntagm_disc,) = cur.fetchone()
	print " %d adc channels, %d tdc channels, and %d discriminator channels linked." % (Ntagm_adc, Ntagm_tdc, Ntagm_disc)


	# ---------------------------------------------------------------------------------------
	# At this point we need to update all entries in the Channel table to
	# fill in the "system" and "col_name" columns. We set this to the table
	# name that has the chanid assigned.
	#
	# This was copied from tt_csv2db.py, but modified to only do it for the TAGM table
	# ---------------------------------------------------------------------------------------

	tablename = "TAGM"

	# Get column names by selecting just first row
	cur.execute("SELECT * FROM %s LIMIT 1" % (tablename))
	colnames = cur.fetchone().keys()
	chan_cols = []
	for colname in colnames:
		if "_chanid" in colname: chan_cols.append(colname)

	# If no rows in table or no columns with names ending in "_chanid" found, continue
	if len(chan_cols)!=0:
	
		print "Updating Channel table for system: %s ..." % (tablename)
		cur.execute("SELECT * FROM %s" % (tablename))
		myrows = cur.fetchall()
		N = 0
		for myrow in myrows:
			for col in chan_cols:
				query = "UPDATE Channel SET system='%s',col_name='%s' WHERE chanid=%d" % (tablename,col,myrow[col])
				cur.execute(query)
				N += 1
		print "Updated %d rows" % N


	print "Done."