#!/usr/bin/python
#
#
#  5/7/2014  David Lawrence
#
#
# This script will modify a tt.db file to fix a problem with 
# the FCAL row and column numbers to shift them to the range 
# 0-58 so that there are no negative numbers. This actually 
# is normally taken care of in the tt_csv2db.py script. However,
# an earlier version of that script was used by the controls
# group at one point to add the controls info so that table
# does not include the correct range, hence this script.
#
# This will first check if the existing range is already correct
# and will only modify the tt.db file if necessary.
#



import sqlite3 as lite
import sys
import os
import re
import datetime


if len(sys.argv) != 2:
	print "You must supply a SQLite DB file to modify!"
	sys.exit(0);

# 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)

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()
	
	# Get existing range of row and col numbers for FCAL
	cur.execute('SELECT MAX(row) AS rowmax,MIN(row) AS rowmin,MAX(col) AS colmax,MIN(col) AS colmin FROM FCAL')
	row = cur.fetchone()
	rowmax = row['rowmax']
	rowmin = row['rowmin']
	colmax = row['colmax']
	colmin = row['colmin']
	print 'Existing table ranges:'
	print '    row = %d to %d' % (rowmin,rowmax)
	print '    col = %d to %d' % (colmin,colmax)
	if(rowmin >= 0 and colmin >=0) :
		print 'This DB appears to up to date already. Leaving it untouched'
		sys.exit(0)
	
	print '\n Applying fix ...'
	cur.execute('SELECT * FROM FCAL')
	rows = cur.fetchall()
	for row in rows:
		irow = row['row'] + 29
		icol = row['col'] + 29
		adc_chanid = row['adc_chanid']
	
		sql = 'UPDATE FCAL SET row=%d,col=%d WHERE adc_chanid=%d' % (irow,icol,adc_chanid)
		cur.execute(sql)
		#print sql

	# Get updated range of row and col numbers for FCAL
	cur.execute('SELECT MAX(row) AS rowmax,MIN(row) AS rowmin,MAX(col) AS colmax,MIN(col) AS colmin FROM FCAL')
	row = cur.fetchone()
	rowmax = row['rowmax']
	rowmin = row['rowmin']
	colmax = row['colmax']
	colmin = row['colmin']
	print 'Updated table ranges:'
	print '    row = %d to %d' % (rowmin,rowmax)
	print '    col = %d to %d' % (colmin,colmax)
	

	print 'Done'