#!/usr/bin/python
#
#
#  3/24/2025  Justin Stevens
#
#
# This script will modify a tt.db file to move FCAL
# lead glass channels to spare ADC 
#
#

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()
    
    ########################
    ## Reference counters ##
    ########################
    
    # get run range ID to add reference counters
    runrange_name = 'RunPeriod-2025-01-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]

    cur.execute("SELECT MAX(chanid) FROM Channel")
    row = cur.fetchone()
    newchanid = row[0]
    
    # list of old channels to be moved [crate,slot,channel]
    preswap = [ [11,4,13], [12,15,0], [13,13,4], [16,10,13], [20,9,7], [21,6,15], [22,7,11], [20,8,8], [20,6,10] ]
    postswap = [ [14,19,13], [14,19,12], [14,19,11], [14,19,10], [17,19,2], [17,19,3], [17,19,4], [17,19,5], [17,19,6] ]
    for old, new in zip(preswap,postswap):

        # disable old channel
        oldrocid = old[0] 
        oldslot = old[1]
        oldchannel = old[2]
        cur.execute("SELECT moduleid from Module,Crate where rocid==%d and slot==%d and module.crateid==crate.crateid" % (oldrocid,oldslot))
        oldmoduleid = cur.fetchone()[0]
        cur.execute("SELECT chanid,name,runrangeid from Channel where moduleid==%d and channel==%d and enable==1 order by runrangeid desc" % (oldmoduleid,oldchannel))
        query = cur.fetchone()
        oldchanid = query[0]
        name = query[1]
        oldrunrangeid = query[2]
        cur.execute("SELECT row,col from FCAL where adc_chanid==%d  order by runrangeid desc" % (oldchanid))
        query = cur.fetchone()
        row = int(query[0])
        col = int(query[1])
        x = col - 29
        y = row - 29

        # if channel exists update it, otherwise insert it
        cur.execute("SELECT chanid from Channel where chanid==%d and runrangeid==%d" % (oldchanid,runrangeid))
        currow = cur.fetchone()
        if currow != None:
            cur.execute("UPDATE Channel set name='FCAL-unused', system='', col_name='', enable=0 where chanid==%d and moduleid==%d and runrangeid=%d" % (oldchanid, oldmoduleid, runrangeid))
        else:
            cur.execute("INSERT INTO Channel Values (%d, %d, 'FCAL-unused', %d, '', '', 0, %d)" % (oldchanid, oldmoduleid, oldchannel, runrangeid))

        # enable new channel
        newrocid = new[0]
        newslot = new[1]
        newchannel = new[2]
        cur.execute("SELECT moduleid from Module,Crate where rocid==%d and slot==%d and module.crateid==crate.crateid" % (newrocid,newslot))
        newmoduleid = cur.fetchone()[0]
        cur.execute("SELECT chanid,name from Channel where moduleid==%d and channel==%d order by runrangeid desc" % (newmoduleid,newchannel))
        if not cur.fetchone():
            newchanid = newchanid + 1

        print("\n %d,%d  %d,%d  (%d,%d,%d) -> (%d,%d,%d) \n" % (x,y, row,col,oldrocid,oldslot,oldchannel,newrocid,newslot,newchannel))
        
        cur.execute("INSERT INTO Channel Values (%d, %d, '%s', %d, 'FCAL', 'adc_chanid', 1, %d)" % (newchanid, newmoduleid, name, newchannel, runrangeid))
        cur.execute("INSERT INTO FCAL VALUES (%d, %d, %d, %d)" % (row, col, newchanid, runrangeid))
                
print('Done')