#!/usr/bin/python

# This script will take a SQLite database file as input and use it
# to create a translation table in XML format.
#
# The original tt.db file that should be used for input was generated
# from information in Fernando's spreadsheet using the script:
# tt_csv2db.py . See comments at the top of that script for details
# on how to run it.
#
# Run this script on the tt.db file:
#
#  ./tt_db2xml.py tt.db
#
#
#  refer questions to:
#
#  David Lawrence x5567
#  davidl@jlab.org
#

import sqlite3 as lite
import sys
import os
import re
import datetime
import time


def Usage():
	print ' '
	print ' Usage:'
	print '       tt_db2xml.py [-hL] tt.db [runrange]'
	print ' '
	print '   options:'
	print '       -h    print usage statement'
	print '       -L    list possible run ranges'
	print ' '
	print ' Use this script to extract a complete GlueX translation table'
	print 'in XML format that can be entered into the CCDB. The XML will'
	print 'contain a subset of information from the sqlite DB file.'
	print ' '
	print ' A run range should usually be specified by the run range name.'
	print 'run this script with the "-L" argument to get a list of available'
	print 'run ranges and their corresponding name. If no runrange is '
	print 'specified, then the most recently defined run range is used.'
	print 'CAUTION: the most recently defined run range may not correspond'
	print 'to the larget run numbers!'
	print ' '
	sys.exit(-1)


# Parse command line arguments
LIST_RUN_RANGES = False
DB_FILENAME     = ''
RUNRANGE        = ''
if len(sys.argv) <2 : Usage()
for arg in sys.argv[1:]:
	if           arg == '-L' : LIST_RUN_RANGES = True
	elif         arg == '-h' : Usage()
	elif DB_FILENAME == ''   : DB_FILENAME = arg
	elif    RUNRANGE == ''   : RUNRANGE = arg
	else:
		Usage()
		sys.exit(-1)


# Connect to SQLite DB file, deleting any existing one first
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()
	
	# Get list of all run ranges
	query = 'SELECT * FROM RunRange ORDER BY runrangeid DESC'
	cur.execute(query)
	c_rows = cur.fetchall()
	if len(c_rows)==0 :
		print '\nERROR: No run ranges defined in DB!\n'
		sys.exit(-1)

	# If a runrange name was specified then find the corresponding runrangeid
	runrangeid = ''
	minRun = ''
	maxRun = ''
	if RUNRANGE != '':
		for row in c_rows:
			if row['name'] == RUNRANGE:
				runrangeid = row['runrangeid']
				minRun = row['minRun']
				maxRun = row['maxRun']
				break
		if runrangeid == '' :
			print '\nUNKNOWN runrange "' + RUNRANGE +'" !'
			print 'Please specify one of the following:'
			LIST_RUN_RANGES = True
	
	# If user just wants list of ranges then print it and exit
	if LIST_RUN_RANGES:
		print '\n-------- Run Ranges -------------'
		for row in c_rows: print row['name'] + ' : ' + str(row['minRun']) + ' - ' + str(row['maxRun'])
		print ' '
		sys.exit(0)

	
	# No runrange name was specified. Get most recent.
	if runrangeid=='': 
		runrangeid = c_rows[0]['runrangeid']
		RUNRANGE   = c_rows[0]['name']
		minRun     = c_rows[0]['minRun']
		maxRun     = c_rows[0]['maxRun']

	# The specified run range may not have an entry for every channel
	# but the channel may have an entry in another run range for which the desired
	# one is completely contained. Make a list of all run range ids for
	# which the specified range is completely contained. Leave the list in
	# order of run range id, but with the targeted range as first element
	query = 'SELECT runrangeid FROM RunRange WHERE minRun<=%d AND maxRun>=%d AND runrangeid!=%d ORDER BY runrangeid DESC' % (minRun, maxRun, runrangeid)
	cur.execute(query)
	d_rows = cur.fetchall()
	runrangeids = [runrangeid]
	for row in d_rows: runrangeids += [int(row[0])]
	print 'The following runrangeids match the specified range and will be searched in this order:'
	print runrangeids

	# Form special clause for queries that selects the first entry corresponding
	# to any of the runrangeids, but in the order of the ids. Note that this
	# actually relies on all of the ids after the first one to be in descending
	# order.
	runrange_clause = "runrangeid in (" + ','.join([str(x) for x in runrangeids]) + ")"
	runrange_clause += " ORDER BY runrangeid=="+str(runrangeid)+" DESC,runrangeid DESC"
	runrange_clause += " LIMIT 1"

	# Get list of valid systems so we don't have to do a query to check for every channel
	query = "SELECT system FROM Channel GROUP BY system"
	cur.execute(query)
	s_rows = cur.fetchall()
	valid_systems = []
	for row in s_rows:
		system = row[0]
		query = "SELECT name FROM sqlite_master WHERE type='table' AND name='%s'" % system;
		cur.execute(query)
		s_rows2 = cur.fetchall()
		if len(s_rows2) >= 1: valid_systems.append( system )

	# Counter to keep track of the number of channels for each system
	Nchannels_by_system = {}
	for s in valid_systems: Nchannels_by_system[s] = 0

	# Get total number unique channels so we can estimate progess for impatient users
	query = "SELECT count(DISTINCT chanid) FROM Channel"
	cur.execute(query)
	row = cur.fetchone()
	Nchannels_db = int(row[0])
	Nchannels_total = 0

	# Start time of job
	start_time = time.time()

	# Open output file and write header
	xmlfilename = "tt.xml"
	if RUNRANGE != '' : xmlfilename = "tt_"+RUNRANGE+".xml"
	print 'Writing to "'+xmlfilename+'" ...'
	of = open(xmlfilename, "w")
	of.write('\n')
	of.write('<!-- GlueX Translation Table for converting between DAQ and Detector coordinates    -->\n')
	of.write('<!-- Created:, ' + datetime.datetime.now().strftime("%Y-%m-%d %H:%M") + '                                                     -->\n')
	of.write('<!-- This file was autogenerated by the script: tt_db2xml.py which can be found at: -->\n')
	of.write('<!-- https://halldsvn.jlab.org/repos/trunk/online/packages/TranslationTable         -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!--        **** DO NOT EDIT THIS FILE AND COMMIT IT TO THE CCDB! ****              -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!-- The information in this file is a subset of the information kept in an SQLite  -->\n')
	of.write('<!-- DB file. Because the SQLite DB must be maintained, permanent changes to the    -->\n')
	of.write('<!-- TT should be made there and this file regenerated from that. The SQLite file   -->\n')
	of.write('<!-- is maintained in the svn repository at the following location:                 -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!-- https://halldsvn.jlab.org/repos/trunk/online/packages/TranslationTable/tt.db   -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!-- Many changes may require a script to change many channels at once. These can   -->\n')
	of.write('<!-- also be used to confirm whether a change has already been applied and to do    -->\n')
	of.write('<!-- integrity checks after application to help ensure the resulting tables are     -->\n')
	of.write('<!-- correct. You can find many examples of such scripts in the TranslationTable    -->\n')
	of.write('<!-- directory in svn mentioned above. Make ABSOLUTELY sure of what you are doing   -->\n')
	of.write('<!-- before making any changes. Please direct any questions to:                     -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!-- David Lawrence                                                                 -->\n')
	of.write('<!-- davidl@jlab.org                                                                -->\n')
	of.write('<!-- (757) 269-5567                                                                 -->\n')
	of.write('<!--                                                                                -->\n')
	of.write('<!-- This file was generated for the run range:                                     -->\n')
	of.write('<!--    ' + RUNRANGE + '  runs: %6d - %6d                                          -->\n' % (minRun, maxRun))
	of.write('\n')
	of.write("<halld_online_translation_table version=\"0.2\">\n")
	
	# Get list of crates from DB
	query = "SELECT * FROM Crate WHERE rocid>0 ORDER BY rocid"
	cur.execute(query)
	c_rows = cur.fetchall()
	for c_row in c_rows:
		rocid = c_row["rocid"]
		crateid = c_row["crateid"]
		print "Writing out tt for rocid %s" % (rocid)
		of.write("  <crate number=\"%d\" type=\"VXS\">\n" % (rocid))

		query = "SELECT * FROM Module WHERE crateid=%s ORDER BY slot" % (crateid)
		cur.execute(query)
		s_rows = cur.fetchall()
		Nchannels = 0
		sys.stdout.write('   slots:')
		for s_row in s_rows:
			moduleid = s_row["moduleid"]
			slot = s_row["slot"]
			type = s_row["type"]
			sys.stdout.write( ' ' + str(slot))
			sys.stdout.flush()
			of.write("    <slot number=\"%s\" type=\"%s\">\n" % (slot, type))

			query = "SELECT * FROM Channel WHERE moduleid=%s GROUP BY chanid ORDER BY channel" % (moduleid)
			cur.execute(query)
			ch_rows = cur.fetchall()
			for ch_row in ch_rows:
				chanid = ch_row["chanid"]
				channel = ch_row["channel"]
				system = ch_row["system"]
				col_name = ch_row["col_name"]
				
				if len(system)<1: continue
				if system not in valid_systems: continue
				
				# Only interested in adc, tdc, and disc channels
				if col_name not in ['adc_chanid', 'tdc_chanid', 'disc_chanid', 'ssp_chanid'] : continue

				# Query channel info for best match of all valid runrangeid valuies
				query = "SELECT * FROM %s" % (system)
				if len(col_name)>0:
					query += " WHERE %s=%s AND %s" % (col_name,chanid,runrange_clause)
				#print "\"%s\"" % (query)
				cur.execute(query)
				row = cur.fetchone()
				try:
					keys = row.keys()
					detindexes = ""
					for key in keys:
						if "_chanid"    in key: continue
						if "runrangeid" in key: continue
						detindexes += " %s=\"%s\"" % (key, row[key])
					
					of.write("      <channel number=\"%s\" detector=\"%s\"%s/>\n" % (channel, system, detindexes))
					Nchannels += 1
					Nchannels_by_system[system] += 1
				except:
					pass
					print "FAILED to find keys for: " + query
					#sys.exit(0)

			of.write("    </slot>\n")
		of.write("  </crate>\n")
		Nchannels_total += Nchannels
		print ' [%d channels  (%3.1f%%)  %3.2f min]' % (Nchannels, 100.0*Nchannels_total/Nchannels_db, (time.time()-start_time)/60.0)

	of.write("</halld_online_translation_table>\n")

	print '\n Number of channels by system:'
	print '----------------------------------'
	maxlen = len(max(Nchannels_by_system.keys(), key=len))
	for k,v in Nchannels_by_system.iteritems(): print ' '*(maxlen+2-len(k)) + k + ': ' + str(v)
	print ''