#!/usr/bin/env python3

import concurrent.futures
import datetime
import logging
import shlex
import subprocess
import sys

import pyodbc

logging.basicConfig(format='[%(asctime)s,%(filename)s,%(levelname)s] %(message)s',
                    datefmt='%Y-%m-%d %H:%M')

def get_schedule(cursor, time, day_mask, date):
    cursor.execute("SELECT id, idCampus, idBuilding, idRoom, TASKNAME, BTNGROUP, BTNNAME FROM tblSchedule "
                   "WHERE BYDAY=1 "
                     "AND (EVENTDAYS & ?) != 0 "
                     "AND EVENTTIME=CAST(? AS time)", (day_mask, time))
    schedule_by_day = cursor.fetchall()

    cursor.execute("SELECT id, idCampus, idBuilding, idRoom, TASKNAME, BTNGROUP, BTNNAME FROM tblSchedule "
                   "WHERE BYDAY=0 "
                     "AND EVENTDATE=CAST(? AS date) "
                     "AND EVENTTIME=CAST(? AS time)", (date, time))
    schedule_by_date = cursor.fetchall()
    
    for event in schedule_by_date:
        cursor.execute("DELETE FROM tblSchedule WHERE id=?", event.id)
    
    return schedule_by_day + schedule_by_date
    
def get_event_info(cursor, event):
    base = ("select tblCampus.ALTNAME as campus, "
                   "tblBuilding.ALTNAME as building, "
                   "tblRoom.ALTNAME as room, "
                   "tblRoom.id as room_id, "
                   "tblRoom.ALTARGS as room_args "
            "from tblCampus join (tblBuilding, tblRoom) "
            "on (tblCampus.id=tblBuilding.idCampus and "
                "tblBuilding.id=tblRoom.idBuilding)")

    if event.idCampus == 0:
        query = base
        cursor.execute(query)
    
    elif event.idBuilding == 0:
        query = (base + " WHERE tblCampus.id=?")
        cursor.execute(query, event.idCampus)
    
    elif event.idRoom == 0:
        query = (base + " WHERE tblBuilding.id=?")
        cursor.execute(query, event.idBuilding)
        
    else:
        query = (base + " WHERE tblRoom.id=?")
        cursor.execute(query, event.idRoom)
    
    return cursor.fetchall()

def get_events(cursor):
    today = datetime.datetime.today()
    date = today.strftime('%Y-%m-%d')
    time = today.strftime('%H:%M')

    day_mask = 0b1000000 >> int(today.strftime('%w'))  # 7-bit mask for day of week
    
    commands, logevents = [], []
    for event in get_schedule(cursor, time, day_mask, date):
        for info in get_event_info(cursor, event):
            query = ("SELECT ALTGROUPNAME, ALTBTNNAME, ALTCOMMAND "
                       "FROM tblButtons "
                       "WHERE idRoom=? AND ALTGROUPNAME=? AND ALTBTNNAME=?")
            cursor.execute(query, (info.room_id, event.BTNGROUP, event.BTNNAME))
            button = cursor.fetchone()
            
            if button is not None:
                groupname, btnname, cmd_sub = button
                cmd_sub = '"' + cmd_sub + '"'
                command = info.room_args.replace('$$CMD', cmd_sub)
                command = shlex.split(command)

                query = ("SELECT ALTNAME FROM tblCmdProcs WHERE id="
                           "(SELECT idCmdProcs FROM tblRoom WHERE id=?)")
                cursor.execute(query, info.room_id)
                cmdproc = list(cursor.fetchone())

                system = "/".join((info.campus, info.building, info.room))
                logevents.append([system, groupname, btnname])
                commands.append(cmdproc + command)
                
    return commands, logevents

def execute_events(db_kwargs, max_workers=5):
    conn = pyodbc.connect(**db_kwargs)
    cursor = conn.cursor()
    
    commands, logevents = get_events(cursor)

    with concurrent.futures.ThreadPoolExecutor(max_workers) as pool:
        if sys.platform.startswith('linux'):
            commands = [[('./' + cmd[0])] + cmd[1:] for cmd in commands]

        futures = [pool.submit(subprocess.call, cmd) for cmd in commands]
        futures = concurrent.futures.as_completed(futures)

        for future, logevent in zip(futures, logevents):
            try:
                future.result()
                logging.info(logevent)
            except Exception as e:
                logging.error(logevent, e)
    
    conn.commit()
    conn.close()

if __name__ == '__main__':
    try:
        with open('/etc/usm/config.json') as config:
            config = json.load(config)
    except FileNotFoundError:
        # use local config for development
        with open(os.path.join('..', '..', 'etc', 'config.json')):
            config = json.load(config)

    execute_events(config['database'])
