#!/usr/bin/python
#
#
#  11/18/2019  Justin Stevens
#
#
# This script will modify a tt.db file to fix a problem with 
# the CCAL where originally the row/col labels were swapped
# and add reference counters.
#
#

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()
	
        # Change order of row and column in CCAL table
        cur.execute('create table CCALFix(col INT, row INT, adc_chanid INTEGER, disc_chanid INTEGER, runrangeid INTEGER DEFAULT 1)')
        cur.execute('insert into CCALFix SELECT col,row,adc_chanid,disc_chanid,runrangeid FROM CCAL')
        cur.execute('DROP table CCAL')
        cur.execute('ALTER TABLE CCALFix RENAME TO CCAL')

        exit

	# Get existing for row and col of known channel
	cur.execute('SELECT row, col FROM CCAL where adc_chanid == 27313')
	row = cur.fetchone()
	if row['row'] == -5 and row['col'] == -6: 
                print 'This DB appears to up to date already. Leaving it untouched'
		sys.exit(0)

        # Do row/col swap if found unswapped test channel
	print '\n Applying CCAL row/col swap ...'
	cur.execute('SELECT * FROM CCAL')
	rows = cur.fetchall()
	for row in rows:
		irow = row['row']
		icol = row['col']
		adc_chanid = row['adc_chanid']
	
		sql = 'UPDATE CCAL SET row=%d,col=%d WHERE adc_chanid=%d' % (icol,irow,adc_chanid)
		cur.execute(sql)
		#print sql

        ########################
        ## Reference counters ##
        ########################

        # get run range ID to add reference counters
        runrange_name = 'RunPeriod-2019-01+'
        cur.execute("SELECT runrangeid FROM RunRange WHERE name='%s'" % runrange_name)
	rows = cur.fetchall()
	if len(rows) == 0:
		print 'Unable to find id for run range with name "%s"!' % runrange_name
		sys.exit(-1)
	runrangeid = rows[0][0]
        
        # get channel and module ID to start counting from
        cur.execute("SELECT name,crateid from Crate where rocid=90")
        row = cur.fetchone()
        cratename = row['name']
        crateid = row['crateid']
	cur.execute("SELECT MAX(chanid) FROM Channel")
	row = cur.fetchone()
	chanid = row[0]+1
        cur.execute("SELECT MAX(moduleid) FROM Module")
	row = cur.fetchone()
	moduleid = row[0]+1
	cur.execute("INSERT INTO Module Values (%d, %d, 1, 'CPU', '')" % (moduleid, crateid))
        moduleid += 1
        cur.execute("INSERT INTO Module Values (%d, %d, 11, 'CTP', '')" % (moduleid, crateid))
        moduleid += 1
        cur.execute("INSERT INTO Module Values (%d, %d, 12, 'SD', '')" % (moduleid, crateid))
        moduleid += 1
        cur.execute("INSERT INTO Module Values (%d, %d, 21, 'TI', '')" % (moduleid, crateid))
        moduleid += 1
        cur.execute("INSERT INTO Module Values (%d, %d, 15, 'fADC250', '')" % (moduleid, crateid))

        # create table and add 2 reference channels
	cur.execute("CREATE TABLE CCAL_REF(id INT, adc_chanid INT, runrangeid INTEGER DEFAULT 1)")

        for channel in range(0,2):
		name = 'CCAL_REF_%d' % channel
                cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'CCAL_REF', 'adc_chanid', 1, %d)" % (chanid, moduleid, cratename, channel, runrangeid))
                cur.execute("INSERT INTO CCAL_REF VALUES (%d, %d, %d)" % (channel+1, chanid, runrangeid))
                chanid += 1

print 'Done'