#!/usr/bin/python
#
#
#  11/05/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
# should be rocid=97 and rocid=98 for rocPS1 and rocPS2 respectively.
# These had been set to rocid=0 originally
#



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 WHERE rocid=97 OR rocid=98')
	s_rows = cur.fetchall()
	if len(s_rows) != 0:
		print 'Entires exist for either rocid=97 or rocid=98. This file was probably'
		print 'already updated. Exiting now without modifying the file.'
		sys.exit(0);	

	# Get crateid for rocPS1 crate.
	cur.execute('SELECT crateid,rocid FROM Crate WHERE name="U1-8-TOP"')
	s_rows = cur.fetchall()
	if len(s_rows) != 1:
		print 'Not exactly 1 entry for crate "U1-8-TOP"!'
		print 'This is unexpected. Exiting now without modifying the file.'
		sys.exit(0);
	(crateid_rocps1,rocid_ps1) = s_rows[0]

	# Get crateid for rocPS2 crate.
	cur.execute('SELECT crateid,rocid FROM Crate WHERE name="U1-8-MID"')
	s_rows = cur.fetchall()
	if len(s_rows) != 1:
		print 'Not exactly 1 entry for crate "U1-8-MID"!'
		print 'This is unexpected. Exiting now without modifying the file.'
		sys.exit(0);
	(crateid_rocps2,rocid_ps2) = s_rows[0]

	print 'Existing ids:'
	print 'rocps1  crateid=%d  rocid=%d' % (int(crateid_rocps1), int(rocid_ps1))
	print 'rocps2  crateid=%d  rocid=%d' % (int(crateid_rocps2), int(rocid_ps2))
	print ''

	# Update rocids
	query = 'UPDATE Crate SET rocid=97 WHERE crateid=%d' % int(crateid_rocps1)
	cur.execute(query)
	query = 'UPDATE Crate SET rocid=98 WHERE crateid=%d' % int(crateid_rocps2)
	cur.execute(query)

	# Now, re-query for the ids and print the updated versions

	# Get crateid for rocPS1 crate.
	cur.execute('SELECT crateid,rocid FROM Crate WHERE name="U1-8-TOP"')
	s_rows = cur.fetchall()
	if len(s_rows) != 1:
		print 'Not exactly 1 entry for crate "U1-8-TOP"!'
		print 'This is unexpected. Exiting now without modifying the file.'
		sys.exit(0);
	(crateid_rocps1,rocid_ps1) = s_rows[0]

	# Get crateid for rocPS2 crate.
	cur.execute('SELECT crateid,rocid FROM Crate WHERE name="U1-8-MID"')
	s_rows = cur.fetchall()
	if len(s_rows) != 1:
		print 'Not exactly 1 entry for crate "U1-8-MID"!'
		print 'This is unexpected. Exiting now without modifying the file.'
		sys.exit(0);
	(crateid_rocps2,rocid_ps2) = s_rows[0]

	print 'Modified ids:'
	print 'rocps1  crateid=%d  rocid=%d' % (int(crateid_rocps1), int(rocid_ps1))
	print 'rocps2  crateid=%d  rocid=%d' % (int(crateid_rocps2), int(rocid_ps2))
	print ''

	print 'Done'