#python imports
import sys, os, time
from contextlib import closing
from datetime import datetime
from itertools import izip

#3rd party imports
import MySQLdb
import cardsharp as cs

#project imports
#sys.path.append(r'C:\Users\jugovich-michael\workspace\bjs_rules')
from ..segment import *
from ..configuration import db_info, config
from .extract import *
from ..util import *
from ..log import *

__all__ = ['db_load']

def db_load(segment, **opt):
    out_dir = os.path.normpath(os.path.join(opt['in_dir'], '..', 'master'))
    
    _out_path = os.path.join(config.out_dir, 'transform')
    if not os.path.isdir(_out_path):
        os.makedirs(_out_path)
                
    if not os.path.isdir(out_dir):
        os.makedirs(os.path.normpath(out_dir))
    
    if opt['out_format'] == 'spss':
        _path = os.path.join(opt['tmp_out_dir'], 'master', 'del_records', segment.name)
        if not os.path.isdir(_path):
            os.makedirs(_path)
            
        out_file = open(os.path.join(_path, 'master.txt'), 'wb')
        
        for file in os.listdir(_path):
            if file == 'master.txt':
                continue
            in_file = open(os.path.join(_path, file), 'rb')
            for line in in_file.readlines():
                out_file.write(line)
            in_file.close()
        out_file.close()
        
        ds = cs.load(source=os.path.join(_path, 'master.txt'), format='text', delimiter='|', line_delimiter='\n')
        ds.save(source=os.path.join(_out_path, '%s_del_records.sav' % segment.name), format='spss', overwrite=True, max_string_width=segment.get_max_length())
    #TODO make this into an outformat option
    #ds.save(source=os.path.join(_out_path, '%s_del_records.xls' % segment.name), format='excel', overwrite=True)
    
    with closing(MySQLdb.connect(host=config.db_host, user=opt['recid_db_info']['user'], passwd=opt['recid_db_info']['pass'], 
                 charset='utf8', db=opt['recid_db_info']['name'], port=config.db_port, use_unicode=True)) as cnx:
        
        with closing(cnx.cursor()) as c:
            #create the path objects and open the file objects
            _file_path = os.path.join(out_dir, '%s.txt' % segment.name)
            _spss_file_path = os.path.join(out_dir, '%s_spss.txt' % segment.name)
            out_file = open(_file_path, 'wb')
            spss_out_file = open(_spss_file_path, 'wb')
            lines = []
            #combine the thread files
            print "Combining %s thread files at %s:" % (segment.name, datetime.now().isoformat(' '))
            for file in os.listdir(opt['in_dir']):
                with closing(open(os.path.join(opt['in_dir'], file), 'rb')) as in_file:
                    for line in in_file.readlines():
                        lines.append(line.split('|'))
            
            print "Sorting %s thread files at %s:" % (segment.name, datetime.now().isoformat(' '))
            for line in sorted(lines, key=lambda k: int(k[0])):
                _line = '|'.join(line)
                out_file.write(_line)
                spss_out_file.write(_line.replace('\\N', ''))
            
            spss_out_file.close()
            out_file.close()
            
            c.execute('SHOW TABLES FROM %s' % opt['recid_db_info']['name'])
            tbl_names = [table[0] for table in c.fetchall()]
            if 'subject' not in tbl_names:
                #check twice to ensure that subject table was not already created by concurrent process 
                time.sleep(2)
                c.execute('SHOW TABLES FROM %s' % opt['recid_db_info']['name'])
                tbl_names = [table[0] for table in c.fetchall()]
                if 'subject' not in tbl_names:
                    c.execute('''CREATE TABLE subject LIKE %s.subject''' % opt['stand_db_info']['name'])
                    #TODO fix this its not working
                    c.execute('''INSERT INTO subject SELECT * FROM %s.subject''' % opt['stand_db_info']['name'])
                    cnx.commit()
                
            if segment.name in tbl_names:
                c.execute('''DROP TABLE %s''' % segment.name)
                cnx.commit()
            
            segment.create_recid_table()
            cs.wait()
            
            _file_path = _file_path.replace('\\', '/')
            
            #for performace boost
            print "Disabling foreign_key_checks at %s:" % (datetime.now().isoformat(' '))
            c.execute('''SET foreign_key_checks=0;''')
            cnx.commit()
            print "Begin %s recidivism database load at %s:" % (segment.name, datetime.now().isoformat(' '))
            c.execute('''LOAD DATA LOCAL INFILE '%s' 
                         INTO TABLE %s 
                         FIELDS TERMINATED BY '|' 
                         ESCAPED BY '\\\\'
                         LINES TERMINATED BY '%s'
                      ''' % (_file_path, segment.name, '\n')
                      )
            cnx.commit()
            print "Fishined %s recidivism database load at %s:" % (segment.name, datetime.now().isoformat(' '))
            c.execute('''SET foreign_key_checks=1;''')
            print "Enabling foreign_key_checks at %s:" % (datetime.now().isoformat(' '))
            cnx.commit()
            if opt['out_format'] == 'spss':
                #if breaking try adding in strip and lower
                print "Saving %s spss file at %s:" % (segment.name, datetime.now().isoformat(' '))
                ds = cs.load(source=_spss_file_path, format='text', delimiter='|', line_delimiter='\n', var_names=[(var.name, var.format) for var in segment.variables])
                
                for var_name, meta in segment.metadata.iteritems():
                    if meta['value_labels']:
                        ds.variables[var_name].value_labels = [(k,v) for k,v in meta['value_labels']] 
                    
                    ds.variables[var_name].label = meta['label']
                    
                ds.save(source=os.path.join(_out_path, '%s.sav' % segment.name), format='spss', overwrite=True, max_string_width=segment.get_max_length())
                #ds.save(source=os.path.join(_out_path, '%s.xls' % segment.name), format='excel', overwrite=True)
                cs.wait()
                print "%s spss file save complete at %s:" % (segment.name, datetime.now().isoformat(' '))
        