#!/usr/bin/python
import MySQLdb
from sndcs.UserFactory import sndcs.UserFactory
from sndcs.AccountPreference import AccountPreference
from sndcs.Store import store

source_host = "localhost"
source_user = "sndcs"
source_password = "sndcs"
source_database = "sndcs2"

source_db = MySQLdb.connect(source_host, source_user, source_password, source_database)
source_cur = source_db.cursor()

###CLASSIDS###
source_cur.execute("SELECT id FROM _MKClassIds WHERE name='Employee'")
Employee = source_cur.fetchall()[0][0]

source_cur.execute("SELECT id FROM _MKClassIds WHERE name='Terminal'")
Terminal = source_cur.fetchall()[0][0]

source_cur.execute("SELECT id FROM _MKClassIds WHERE name='Department'")
Department = source_cur.fetchall()[0][0]

source_cur.execute("SELECT id FROM _MKClassIds ORDER BY id DESC LIMIT 1")
classIdNum = source_cur.fetchall()[0][0] + 1

###ADD TABLES###
tablesToAdd = { "DepartmentByEmployee": { "employee": Employee, "department": Department }, "DepartmentByTerminal": { "terminal": Terminal, "department": Department } }

###ADD COLUMNS###
columnsToAdd = { "Operation": { "estHours": "FLOAT","estSetupHours": "FLOAT" }, "Routing":  {"estHours": "FLOAT","estSetupHours": "FLOAT" }, "AccountPreference": { "lateEmployees": "BOOL" } }

###DELETE COLUMNS###
columnsToDelete = { "Department": [ "employeeObjId", "employeeClassId", "terminalClassId", "terminalObjId" ] }
#columnsToDelete.update({ "JobHed": [ "qtyScrap", "qtyComplete" ] }) #We don't used these anymore?!

for k,v in tablesToAdd.items():
    source_cur.execute("INSERT INTO _MKClassIds VALUES(%d,'%s')" % (classIdNum,k) )
    source_cur.execute("CREATE TABLE %s (serialNum INT(11) DEFAULT NULL AUTO_INCREMENT, PRIMARY KEY(serialNum) )" % k)
    for column,value in v.items():
        source_cur.execute("ALTER TABLE %s ADD COLUMN %sObjId INT(10) UNSIGNED DEFAULT NULL" % (k,column) ) 
        source_cur.execute("ALTER TABLE %s ADD COLUMN %sClassId INT(10) UNSIGNED DEFAULT %d" % (k,column,eval("%s" % column.capitalize() ) ) )
        
    classIdNum += 1

source_cur.execute("SELECT employeeObjId,code FROM Department WHERE employeeObjId IS NOT NULL")
for x in source_cur.fetchall():
    source_cur.execute("SELECT serialNum FROM Department WHERE code = '%s' AND employeeObjId IS NULL AND terminalObjId IS NULL" %  x[1])
    try:
        department = source_cur.fetchall()[0][0]
        print "Employee serialNum:",x[0],"Department serialNum:",department
        source_cur.execute("INSERT INTO DepartmentByEmployee (employeeObjId,departmentObjId) VALUES(%s,%s)" % (x[0],department) )
    except IndexError:
        pass

source_cur.execute("DELETE FROM Department WHERE employeeObjId IS NOT NULL")

source_cur.execute("SELECT terminalObjId,code FROM Department WHERE terminalObjId IS NOT NULL")
for x in source_cur.fetchall():
    source_cur.execute("SELECT serialNum FROM Department WHERE code = '%s' AND employeeObjId IS NULL AND terminalObjId IS NULL" % x[1])
    try:
        department = source_cur.fetchall()[0][0]
        print "Terminal serialNum:",x[0],"Department serialNum:",department
        source_cur.execute("INSERT INTO DepartmentByTerminal (terminalObjId,departmentObjId) VALUES(%s,%s)" % (x[0],department) )
    except IndexError:
        pass

source_cur.execute("DELETE FROM Department WHERE terminalObjId IS NOT NULL")

for k,v in columnsToDelete.items():
   for column in v:
        source_cur.execute("ALTER TABLE %s DROP COLUMN %s" % (k,column) ) 

for k,v in columnsToAdd.items():
    for column, typeOf in v:
        source_cur.execute("ALTER TABLE %s ADD COLUMN %s %s DEFAULT NULL" % (k,column,typeOf) )
    source_cur.execute("UPDATE %s SET %s = 0" % (k,column) )

for x in UserFactory.getUsers():
    if not x.preferences():
        preference = AccountPreference()
        x.addToPreferences(preference)
        store.saveChanges()


print
print "Update successful...."