#!/usr/bin/python
#
# This script adds the FWMPC fADC125 and CTOF fADC250 to the translation table (mapping Alex/Beni).
#
# This is intended as a one time script that will add the info. to the
# tt.db file where it will be maintained. 

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 that this TT file has not already been updated
	cur.execute("SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'FWMPC'")
	rf_rows = cur.fetchall()
	if len(rf_rows) != 0:
		print 'FMWPC table was already created'
		sys.exit(-1)

	print 'Write FWMPC table'

	# Get max chanid already in DB and set our min_chanid to be one more than that
	cur.execute("SELECT MAX(chanid) FROM Channel")
	row = cur.fetchone()
	min_chanid = row[0]+1
	print 'min_chanid = ' + str(min_chanid)

        # Get max crateid already in DB and set our values for our FMWPC modules based on that
	cur.execute("SELECT MAX(crateid) FROM Crate")
	row = cur.fetchone()
	crateid_min = row[0]+1
	print 'FMWPC Crate ID: %d' % crateid_min

	# Get max moduleid already in DB and set our values for our FMWPC modules based on that
	cur.execute("SELECT MAX(moduleid) FROM Module")
	row = cur.fetchone()
	moduleid_min = row[0]+1
	print 'First fADC125 module ID: %d' % moduleid_min

	# Get runrangeid based on name. This should be created ahead of time via:
	#    hd_tt_tool.py runrange create 100000 inf RunPeriod-2022-05-default
	runrange_name = 'RunPeriod-2022-05-default'
	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]
	print 'runrangeid = ' + str(runrangeid)

	# Add crates (crateid|name|area|rack|location|SN|Function|rocid|host|IP)
        cratename = 'D2-9-FMWPC'
	cur.execute("INSERT INTO Crate Values (%d, '%s', 'D2', 9, 'FWMPC', '', 'fADC125', 80, '', '129.57.153.100')" % (crateid_min, cratename))
        
	# Add modules for FWMPC fADC modules (moduleid|crateid|slot|type|SN)
        for module in range(0,14):
                moduleid = moduleid_min + module
                slotid = 3 + module
                if slotid == 11 or slotid == 12:
                        continue
	        cur.execute("INSERT INTO Module Values (%d, %d, %d, 'fADC125', '')" % (moduleid, crateid_min, slotid))

        # make FMWPC table for inserting
	cur.execute("CREATE TABLE FMWPC(layer INT, wire INT, adc_chanid INT, runrangeid INTEGER DEFAULT 1)")

        # global channel ID that should be incremented for all channels
        chanid = min_chanid

        # loop over FWMPC channels to insert
        for ilayer in range(1,7):
                wire = 0
                for islot in range(1,3):
                        for ichan in range(0,72):
                                layer = ilayer
                                slot = (ilayer-1)*2 + islot + 2
                                if layer > 4:
                                        slot += 2 # skip 2 slots mid-crate
                                wire += 1
                                channel = ichan
                                name = "FWMPC-%d-%d" % (layer, wire)

                                #print(layer, slot, wire, ichan)

                                cur.execute("SELECT moduleid from Module where crateid=%d and slot=%d" % (crateid_min,slot))
                                row = cur.fetchone()
				#print(row, layer, slot)
                                moduleid = row['moduleid']

                                cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'FMWPC', 'adc_chanid', 1, %d)" % (chanid, moduleid, name, channel, runrangeid))

                                cur.execute("INSERT INTO FMWPC Values (%d, %d, %d, %d)" % (layer, wire, chanid, runrangeid))
                                chanid += 1

        ######################################################
        ######################## CTOF ########################
        ######################################################

        # make CTOF table for inserting (matches TOF naming scheme)
	cur.execute("CREATE TABLE CTOF(layer INT, bar INT, end TEXT, adc_chanid INT, tdc_chanid INT, disc_chanid INT, runrangeid INTEGER DEFAULT 1)")

        # get channel and module ID for ADC
        cratename = 'D2-1-TOP'
        rocid = 77 
        slot = 19 ########### May need update from Beni ###########
        cur.execute("SELECT moduleid from Module,Crate where rocid==%d and slot==%d and module.crateid==crate.crateid" % (rocid,slot))
        row = cur.fetchone()
        moduleid = row['moduleid']

        # loop over ADC channels to add
        names = ['CTOF-UP-A-1', 'CTOF-UP-A-2', 'CTOF-UP-A-3', 'CTOF-UP-A-4', 'CTOF-DW-A-1', 'CTOF-DW-A-2', 'CTOF-DW-A-3', 'CTOF-DW-A-4']
        adc_chanids = []
        channel = 0
        for name in names:
                cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'CTOF', 'adc_chanid', 1, %d)" % (chanid, moduleid, name, channel, runrangeid))
                adc_chanids.append(chanid)
                chanid += 1
                channel += 1
	
        # get channel and module ID for TDC
        cratename = 'D2-1-MID'
        rocid = 78
        slot = 9 ########### May need update from Beni ###########
        cur.execute("SELECT moduleid from Module,Crate where rocid==%d and slot==%d  and module.crateid==crate.crateid" % (rocid,slot))
        row = cur.fetchone()
        moduleid = row['moduleid']
        
        # update module type for this slot to be consistent with TDC VX1290A
        cur.execute("UPDATE Module SET TYPE='VX1290A' WHERE MODULEID=%d" % moduleid)

        # loop over TDC channels to add
        names = ['CTOF-UP-T-1', 'CTOF-UP-T-2', 'CTOF-UP-T-3', 'CTOF-UP-T-4', 'CTOF-DW-T-1', 'CTOF-DW-T-2', 'CTOF-DW-T-3', 'CTOF-DW-T-4']
        tdc_chanids = []
        channel = 0
        for name in names:
                cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'CTOF', 'tdc_chanid', 1, %d)" % (chanid, moduleid, name, channel, runrangeid))
                tdc_chanids.append(chanid)
                chanid += 1
                channel += 1

        # get channel and module ID for DISC
        cratename = 'D2-1-BOT'
        rocid = 79

        # Add new DISC for CTOF modules (moduleid|crateid|slot|type|SN)
        moduleid = moduleid_min + 8
        slot = 16 ########### May need update from Beni ###########
        cur.execute("INSERT INTO Module Values (%d, %d, %d, 'DISC', '')" % (moduleid, 63, slot))

        cur.execute("SELECT moduleid from Module,Crate where rocid==%d and slot==%d  and module.crateid==crate.crateid" % (rocid,slot))
        row = cur.fetchone()
        moduleid = row['moduleid']

        # loop over DISC channels to add
        names = ['CTOF-UP-D-1', 'CTOF-UP-D-2', 'CTOF-UP-D-3', 'CTOF-UP-D-4', 'CTOF-DW-D-1', 'CTOF-DW-D-2', 'CTOF-DW-D-3', 'CTOF-DW-D-4']
        disc_chanids = []
        channel = 1
        for name in names:
                cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'CTOF', 'disc_chanid', 1, %d)" % (chanid, moduleid, name, channel, runrangeid))
                disc_chanids.append(chanid)
                chanid += 1
                channel += 1

        # finally insert into channel table
        counter = 0
        for adc_chanid, tdc_chanid, disc_chanid in zip(adc_chanids, tdc_chanids, disc_chanids):
                end = "UP"
                if counter > 3: end = "DW" 
                cur.execute("INSERT INTO CTOF VALUES (%d, %d, '%s', %d, %d, %d, %d)" % (0, counter%4 + 1, end, adc_chanid, tdc_chanid, disc_chanid, runrangeid))
                counter += 1