
__all__ = ['StoredProcedures']


class StoredProcedures(object):
    def __init__(self, cursor, **options):
        self.c = cursor
        self.opt = options
    
    def get_tables(self):
        return self.c.execute("SHOW TABLES FROM %s" % self.opt['db_info']['name']).fetchall()
    
    def get_casenum_length(self):
        return self.c.execute("""SELECT MAX(LENGTH(id)) FROM subject""").fetchall()
    
    def create_table(self, tbl_name, var_names):
        v = ','.join(['%s TEXT' % n for n in var_names])
        self.c.execute('''CREATE TABLE %s (id int(10) AUTO_INCREMENT, %s, PRIMARY KEY(id))''' % (tbl_name, v))
                       
    def drop_tables(self, tbl_name):
        for tbl in [tbl_name, '%sx' % tbl_name]:
            if tbl in [tbl[0] for tbl in self.get_tables()]:
                c.execute("DROP TABLE %s" % tbl)
    
    def load_raw(self, name, var_names, line_delim, cols, set_statement):
        path = os.path.join(self.opt['in_dir'], '%s.%s' % (name, self.opt['in_format'])).replace('\\', '/')
        
        self.c.execute('''
         LOAD DATA LOCAL INFILE '%s' 
         INTO TABLE %s 
         FIELDS TERMINATED BY '%s' ESCAPED BY ''
         LINES TERMINATED BY '\r\n'
         (%s) 
         SET %s
        ''' % (path, 
               '%sx' % name, 
               "'%s'" % opt.get('delimiter', '|'), 
               '(%s)' % ','.join(['@v%s' % x for x in xrange(len(var_names))]), 
               'id=\N, %s' % ','.join(['%s = @v%s' % (name,i) for i, name in enumerate(var_names)])
               )
        )
        
    def alter_arrest(self, col_lengths):
        self.c.execute('''
         ALTER TABLE arrestx 
         ADD COLUMN casenum int(%i) 
             AFTER id,
             ADD CONSTRAINT arrestx_casenum 
             FOREIGN KEY (casenum) 
             REFERENCES Subject(id),
         ADD COLUMN astate int(%i) 
             AFTER arrstatex,
             ADD CONSTRAINT arrestx_state 
             FOREIGN KEY (astate) REFERENCES region(id),
         ADD COLUMN asource int(%i) AFTER rapstatex,
             ADD CONSTRAINT arrestx_source 
             FOREIGN KEY (asource) REFERENCES source(id),
         ADD COLUMN arrname nvarchar(%i) AFTER arrdatex, 
         ADD COLUMN afed int(%i) AFTER ynfederalx,
             ADD CONSTRAINT arrestx_fed 
             FOREIGN KEY (afed) REFERENCES fed(id),
         ADD COLUMN cycnum int(%i) AFTER cycnumx,
         ADD COLUMN aseqnum int(%i) AFTER aseqnumx,
         ADD COLUMN aori_invalid tinyint(1) AFTER arrnamex,
         ADD COLUMN aori nvarchar(%i) AFTER aorix
         ''' % tuple(col_lengths)
         )
    
    def alter_sentence(self, col_lengths):
        #TODO after updating mysql driver make into tinyint
        self.c.execute('''
         ALTER TABLE sentencex 
         ADD COLUMN casenum int(%i) 
             AFTER id, 
             ADD CONSTRAINT sentencex_casenum 
             FOREIGN KEY (casenum) 
             REFERENCES Subject(id),
         ADD COLUMN cstate int(%i) 
             AFTER crtstatex, 
             ADD CONSTRAINT sentencex_state 
             FOREIGN KEY (cstate) 
             REFERENCES region(id),
         ADD COLUMN csource int(%i) 
             AFTER rapstatex,
             ADD CONSTRAINT sentencex_source 
             FOREIGN KEY (csource) 
             REFERENCES source(id),
         ADD COLUMN cfed int(%i) 
             AFTER ynfederalx,
             ADD CONSTRAINT sentencex_fed 
             FOREIGN KEY (cfed) 
             REFERENCES fed(id),
         ADD COLUMN cycnum int(%i) 
             AFTER cycnumx,
         ADD COLUMN cseqnum int(%i) 
             AFTER cseqnumx,
         ADD COLUMN cori nvarchar(%i) 
             AFTER crtnamex,
         ADD COLUMN cori_invalid tinyint(1) 
             AFTER corix
         ''' % tuple(col_lengths)
         )
    
    def alter_supervision(self, col_lengths):
        self.c.execute('''
         ALTER TABLE supervisionx 
         ADD COLUMN casenum int(%i) AFTER id, 
         ADD CONSTRAINT supervisionx_casenum FOREIGN KEY (casenum) REFERENCES Subject(id),
         ADD COLUMN cycnum int(%i) AFTER cycnumx
         ''' % tuple(col_lengths)
        )
    
    def alter_demographic(self, col_lengths):
        self.c.execute('''
            ALTER TABLE demographicx 
            ADD COLUMN casenum int(%i) AFTER id, 
            ADD CONSTRAINT demographicx_casenum FOREIGN KEY (casenum) REFERENCES Subject(id)
        ''' % tuple(col_lengths))