from itertools import izip, izip_longest
from ..configuration import config
import MySQLdb

__all__ = ['get_max_cycles', 'extract', 'get_max_profiles']

def get_max_cycles(db_info):
    #open the connection to the database
    db = MySQLdb.connect(host=config.db_host, user=db_info['user'], passwd=db_info['pass'], 
                         charset='utf8', db=db_info['name'], port=config.db_port, use_unicode=True)
    conn_cursor = db.cursor()
    
    #get the highest cycle
    conn_cursor.execute("SELECT max(cycle) FROM arrest")
    max_cycle = conn_cursor.fetchall()[0]
    
    #close the database connection
    conn_cursor.close()
    db.close()
    
    return max_cycle[0]

def get_max_profiles(db_info):
    #open the connection to the database
    db = MySQLdb.connect(host=config.db_host, user=db_info['user'], passwd=db_info['pass'], 
                         charset='utf8', db=db_info['name'], port=config.db_port, use_unicode=True)
    conn_cursor = db.cursor()
    
    #get the highest cycle
    conn_cursor.execute("SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM %s.demographicx GROUP BY casenumx) AS t" % db_info['name'].replace('_recid', ''))
    max_profile = conn_cursor.fetchall()[0][0]
    
    #close the database connection
    conn_cursor.close()
    db.close()
    
    return max_profile
    
def extract(seg_name, casenum, db):
    #create a database cursor
    conn_cursor = db.cursor()
    
    #get the records from the recidivism database
    conn_cursor.execute("""SELECT * FROM %s WHERE casenum = %s""" % (seg_name, casenum))
    records = conn_cursor.fetchall()
    
    #get the column names from the database
    conn_cursor.execute('SHOW COLUMNS FROM %s' % seg_name)
    columns = [col[0] for col in conn_cursor.fetchall()]
    
    #if there are no records for a given subject
    if not records:
        #get a copy of columns
        _columns = []
        _columns.extend(columns)
        
        #set the casenum on resultset with no records
        records = [casenum]
        #pop from columns because we added casenum
        _columns.pop()
        
        #set None to the remaining columns
        records.extend([record for record,col in izip_longest(records, columns)])
        records = [records]
    
    #close the cursor
    conn_cursor.close()
    
    #create a dictionary of dictionaries: dict(id_of_record : dict( col_name : col_value for each column))
    return dict([(record[0], dict([(c, r) for c,r in izip(columns, record)])) for record in records])
