#!/usr/bin/python
#
#
#  4/29/2014  David Lawrence
#
#
# This script will modify a tt.db file to fix a problem with 
# the adc chanid values used. At some point, the Channel and ST
# tables were changed to link the ADC channels for the ST to the
# corresponding ISEG channel (by mistake).
#
# This also removes some erroneous entries that have existed
# since the first version of the tt.db file where the name is
# a long string with several names and lots of spaces.
#



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()
	
	# Remove entries with long names that are clearly mistakes
	cur.execute('DELETE FROM Channel WHERE name LIKE "ST-%" AND length(name)>10')

	# Loop over all sectors and update ADC and ISEG channel entries
	for sector in range(1,31):
	
		# Get chanid for the ADC channel
		sql = 'SELECT chanid FROM Channel WHERE name="ST-A-%d"' % sector
		cur.execute(sql)
		row = cur.fetchone()
		chanid = row['chanid']
		
		sql = 'UPDATE ST SET adc_chanid=%d WHERE sector=%d' % (chanid, sector)
		cur.execute(sql)
		
		sql = 'UPDATE Channel SET system="ST",col_name="adc_chanid" WHERE chanid=%d' % chanid
		cur.execute(sql)
		
		# Get chanid for the ISEG channel
		sql = 'SELECT chanid FROM Channel WHERE name="ST-%d"' % sector
		cur.execute(sql)
		row = cur.fetchone()
		chanid = row['chanid']

		sql = 'UPDATE Channel SET system="",col_name="" WHERE chanid=%d' % chanid
		cur.execute(sql)



	print 'Done'