#!/usr/bin/python
#
#
#  4/11/2014  David Lawrence
#
#
# This script will modify a tt.db file to use the correct rocid values
# as documented on the wiki page here:
#
# https://halldweb1.jlab.org/wiki/index.php/HallD_Online_IP_Name_And_Address_Conventions
#
#  and the jcedit export/import file here:
#
# svn://gluon50/trunk/svn.cool/gluex/jcedit/component_db.xml
#
# Note that in the case of a descrepancy, the jcedit file should probably take
# precedence since it should be what the DAQ system is actually using.
#
# It is OK to run this script on a SQLite file that has already been updated.
#
#
# The top part of this script statically defines the rocid based on the 
# crate location. This code was generated using the following procedure:
#
# 1.) Copy the table contents of the table from the above mentioned wiki
#     page and paste them into a spreadsheet.
#
# 2.) In the spreadsheet, make copies of the rocid, Geographic Location,
#     and Crate Name columns. Do this just using the "=" function so that
#     cells in the wiki table that are empty will instead have a "0"
#     in the copied column.
#
# 3.) Copy the 3 columns created in step 2.) and paste them into a text 
#     file. I named this text file "GeoLocation_to_crateName.txt"
#
# 4.) Run the following command to print the desired information to
#     the screen in the form of the python code below.
#
#  cat GeoLocation_to_crateName.txt | awk '{print "rocid_map[\""$2"\"] = \""$1"\";  // "$3}'
#
#
# 5.) Copy the generated code and paste it into this script

rocid_map = {}
#---------------- Auto-generated code ---------------------
rocid_map["T1-1-TOP"] = "71";  # TAG1
rocid_map["T1-1-BOT"] = "72";  # TAG2
rocid_map["T1-2-TOP"] = "74";  # TAG4?
rocid_map["T1-2-MID"] = "73";  # TAG3?
rocid_map["T1-2-BOT"] = "75";  # TAG5?
rocid_map["T1-3-TOP"] = "0";  # 0
rocid_map["T1-3-MID"] = "0";  # 0
rocid_map["U1-7-TOP"] = "2";  # STPSC1
rocid_map["U1-7-BOT"] = "0";  # 0
rocid_map["U1-8-TOP"] = "0";  # PS1
rocid_map["U1-8-MID"] = "0";  # PS2
rocid_map["U1-8-BOT"] = "3";  # STPSC2
rocid_map["U1-9-TOP"] = "0";  # 0
rocid_map["U1-10-TOP"] = "0";  # 0
rocid_map["N1-1-TOP"] = "0";  # 0
rocid_map["N1-2-BOT"] = "43";  # BCAL13
rocid_map["N1-3-TOP"] = "34";  # BCAL4
rocid_map["N1-3-MID"] = "35";  # BCAL5
rocid_map["N1-3-BOT"] = "36";  # BCAL6
rocid_map["N1-4-TOP"] = "31";  # BCAL1
rocid_map["N1-4-MID"] = "32";  # BCAL2
rocid_map["N1-4-BOT"] = "33";  # BCAL3
rocid_map["N1-5-BOT"] = "44";  # BCAL14
rocid_map["N1-6-TOP"] = "0";  # 0
rocid_map["S1-1-TOP"] = "0";  # 0
rocid_map["S1-2-BOT"] = "45";  # BCAL15
rocid_map["S1-3-TOP"] = "37";  # BCAL7
rocid_map["S1-3-MID"] = "38";  # BCAL8
rocid_map["S1-3-BOT"] = "39";  # BCAL9
rocid_map["S1-4-TOP"] = "40";  # BCAL10
rocid_map["S1-4-MID"] = "41";  # BCAL11
rocid_map["S1-4-BOT"] = "42";  # BCAL12
rocid_map["S1-5-BOT"] = "46";  # BCAL16
rocid_map["S1-6-TOP"] = "0";  # 0
rocid_map["U2-1-TOP"] = "0";  # 0
rocid_map["U2-2-BOT"] = "0";  # 0
rocid_map["U2-3-TOP"] = "25";  # CDC1
rocid_map["U2-3-MID"] = "26";  # CDC2
rocid_map["U2-3-BOT"] = "51";  # FDC1
rocid_map["U2-4-TOP"] = "52";  # FDC2
rocid_map["U2-4-MID"] = "53";  # FDC3
rocid_map["U2-4-BOT"] = "54";  # FDC4
rocid_map["U2-5-TOP"] = "55";  # FDC5
rocid_map["U2-5-MID"] = "56";  # FDC6
rocid_map["U2-5-BOT"] = "57";  # FDC7
rocid_map["U2-6-TOP"] = "58";  # FDC8
rocid_map["U2-6-MID"] = "59";  # FDC9
rocid_map["U2-6-BOT"] = "60";  # FDC10
rocid_map["U2-7-TOP"] = "61";  # FDC11
rocid_map["U2-7-MID"] = "62";  # FDC12
rocid_map["U2-7-BOT"] = "63";  # FDC13
rocid_map["U2-8-TOP"] = "27";  # CDC3
rocid_map["U2-8-MID"] = "28";  # CDC4
rocid_map["U2-8-BOT"] = "64";  # FDC14
rocid_map["U2-9-MID"] = "0";  # 0
rocid_map["U2-10-TOP"] = "0";  # 0
rocid_map["D2-1-TOP"] = "77";  # TOF1
rocid_map["D2-1-MID"] = "78";  # TOF2
rocid_map["D2-1-BOT"] = "0";  # 0
rocid_map["D2-2-TOP"] = "11";  # FCAL1
rocid_map["D2-2-MID"] = "12";  # FCAL2
rocid_map["D2-2-BOT"] = "13";  # FCAL3
rocid_map["D2-3-TOP"] = "14";  # FCAL4
rocid_map["D2-3-MID"] = "15";  # FCAL5
rocid_map["D2-3-BOT"] = "16";  # FCAL6
rocid_map["D2-6-TOP"] = "17";  # FCAL7
rocid_map["D2-6-MID"] = "18";  # FCAL8
rocid_map["D2-6-BOT"] = "19";  # FCAL9
rocid_map["D2-7-TOP"] = "20";  # FCAL10
rocid_map["D2-7-MID"] = "21";  # FCAL11
rocid_map["D2-7-BOT"] = "22";  # FCAL12
rocid_map["D2-8-TOP"] = "0";  # 0
#----------------------------------------------------------

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, deleting any existing one first
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()

	# Loop over all crates defined at top of this file
	Ncrates_in_db = 0
	Ncrates_with_bad_rocid = 0
	Nalready_correct = 0
	Nupdated = 0
	Nbad_rocid_row_count = 0
	for location in rocid_map:
		rocid = rocid_map[location]
		if int(rocid) == 0:
			Ncrates_with_bad_rocid += 1
			continue;  # skip crates with rocid=0
		
		# Get number of rows with this location (should just be 1!)
		sql = 'SELECT rocid FROM Crate WHERE name="%s"' % location
		cur.execute(sql)
		s_rows = cur.fetchall()
		
		# Verify there is exactly one row with this crate name
		if len(s_rows) != 1:
			print 'Number of entries for %s is not one!' % location
			continue

		Ncrates_in_db += 1

		# If the value is already correct, then do not update
		if int(rocid) == int(s_rows[0]['rocid']):
			Nalready_correct += 1
			continue

		#print "found rocid=%s in %s (will set to %s)" % (s_rows[0]['rocid'], db_filename, rocid)

		
		# Any rows that already have our rocid should have it updated
		# so we don't have multiple entries with the same rocid. Set
		# these to 0.
		sql = 'UPDATE Crate SET rocid="0" WHERE rocid="%s"' % rocid
		cur.execute(sql)
		
		# Update Crate table
		sql = 'UPDATE Crate SET rocid=%s WHERE name="%s"' % (rocid, location)
		Nupdated += 1
		cur.execute(sql)
	
	# Loop over all locations again to verify that each rocid shows up
	# in only one row in the Crate table
	for location in rocid_map:
		rocid = rocid_map[location]
		if int(rocid) == 0:
			continue;  # skip crates with rocid=0
	
		sql = "SELECT name,rocid,count(rocid) AS cnt FROM Crate WHERE rocid='%s' GROUP BY rocid" % rocid
		cur.execute(sql)
		s_rows = cur.fetchall()
		if len(s_rows)!=1:
			print "ERROR: Num rows for rocid=%s not 1! (cnt=%d)" % (rocid, rocid, s_rows[0]['cnt']) 
			Nbad_rocid_row_count += 1
	
	# Report statistics
	print "    Number of entries defined in script: %d" % len(rocid_map)
	print "      Number with bad rocid (e.g. zero): %d" % Ncrates_with_bad_rocid
	print "                 Entries found in %s: %d" % (db_filename, Ncrates_in_db)
	print "                              should be: %d" % (len(rocid_map)-Ncrates_with_bad_rocid)
	print "Number of entries in DB already correct: %d" % Nalready_correct
	print " Num. bad rocid row counts after update: %d" % Nbad_rocid_row_count
	print "                   Number rows modified: %d" % Nupdated