#!/usr/bin/python
#
#
#  11/19/2014  David Lawrence
#
#
# This script will modify a tt.db file to fix a problem with 
# the rocid for the two crates used by the PS. These originally
# were set to 0, then "fixed" by the tt_fix_ps.py script to have
# rocid's 97 and 98. Only to realize later that the IP addresses
# assigned to these were 83 and 84. This script fixes the problem
# by changing:
#               rocid=97 -> rocid=83
#               rocid=98 -> rocid=84
#



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

	# Check if this has already been updated
	cur.execute('SELECT * FROM Crate,Module WHERE Module.crateid=Crate.crateid AND rocid=72 AND type=\'fADC250\'')
	s_rows = cur.fetchall()
	if len(s_rows) != 15:
		print 'Did not find exactly 15 fADC250 modules with rocid==72.'
		print 'This file may have already been updated.'
		print 'Exiting now without modifying the file.'
		sys.exit(0);	

	# To convert rocid's, we need to first change them to values
	# outside the range of all rocid's
	query = 'UPDATE Crate SET rocid=7200 WHERE rocid=74' 
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=7300 WHERE rocid=72' 
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=7400 WHERE rocid=75' 
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=7500 WHERE rocid=73' 
	print query
	cur.execute(query)
	
	# Now, convert back to correct rocid's
	query = 'UPDATE Crate SET rocid=72 WHERE rocid=7200'
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=73 WHERE rocid=7300'
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=74 WHERE rocid=7400'
	print query
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=75 WHERE rocid=7500'
	print query
	cur.execute(query)


	# Get crateid for rocPS1 crate.
	cur.execute('SELECT * FROM Crate,Module WHERE Module.crateid=Crate.crateid AND rocid=73 AND type=\'fADC250\'')
	s_rows = cur.fetchall()
	if len(s_rows) != 15:
		print 'Did not find exactly 15 modules with rocid==73'
		print 'after update! What happened! You\'re going to need to go'
		print 'in and figure out the mess I left by hand.'
	else:
		print 'Operation appears to have succeeded!'

	print 'Done'