import sys, os
from contextlib import closing
#TODO add cmd calls
sys.path.append(r'D:\harmony\src')
from util import *

from configuration import db_info, config
import cardsharp as cs 
__all__ = ['count_states']


def count_states(**opt):
    with closing(get_cnxn(opt['db_info']['name'],opt)) as cnx:
        with closing(cnx.cursor()) as c:
            c.execute("select id,state from subject")
            subject = c.fetchall()
            #===================================================================
            # a_count_ds = cs.Dataset([('id', 'integer'), ('release_state', 'integer'), ('state_count', 'integer'), ('most_frequent_chg16', 'integer')])
            # c_count_ds = cs.Dataset([('id', 'integer'), ('release_state', 'integer'), ('state_count', 'integer'), ('most_frequent_chg16', 'integer')])
            # for id, state in subject:
            #    c.execute("select count(distinct(astate)), max(achg16) from arrest where casenum = %i" % id)
            #    result = c.fetchall()
            #    a_count_ds.add_row([id, state, result[0][0], result[0][1]])
            #    c.execute("select count(distinct(cstate)), max(cchg16) from sentence where casenum = %i" % id)
            #    result = c.fetchall()
            #    c_count_ds.add_row([id, state, result[0][0], result[0][1]])
            #    
            # a_count_ds.save(source=r'P:\6684\Common\bjs\data\prd_out\stats\arrest_count_states.txt', format='text')
            # c_count_ds.save(source=r'P:\6684\Common\bjs\data\prd_out\stats\sentence_count_states.txt', format='text')
            #===================================================================
            
            with closing(open('output/arrest_count_states.txt', 'wb')) as file:
                file.write('%s\n' % '\t'.join(['id', 'release_state', 'state_count', 'most_frequent_chg16']))
                for id, state in subject:
                    c.execute("select count(distinct(astate)), max(achg16) from arrest where casenum = %i" % id)
                    result = c.fetchall()
                    file.write('%s\n' % '\t'.join([str(id), str(state), str(result[0][0]), str(result[0][1] if result[0][1] is not None else '')]))
            
            with closing(open('output/sentence_count_states.txt', 'wb')) as file:
                file.write('%s\n' % '\t'.join(['id', 'release_state', 'state_count', 'most_frequent_chg16']))
                for id, state in subject:
                    c.execute("select count(distinct(cstate)), max(cchg16) from sentence where casenum = %i" % id)
                    result = c.fetchall()
                    file.write('%s\n' % '\t'.join([str(id), str(state), str(result[0][0]), str(result[0][1] if result[0][1] is not None else '')]))
    
def count_missing(**opt):
    ds = cs.Dataset('id')
    with closing(get_cnxn(opt['db_info']['name'],opt)) as cnx:
        with closing(cnx.cursor()) as c:
            for segment in ['demographic', 'arrest', 'sentence', 'supervision']:
                c.execute("""select id, state from subject 
                             where id not in (select casenum from %s)""" % segment)
                subject = c.fetchall()
                with closing(open('output/no_%s_info.txt' % segment, 'wb')) as file:
                    file.write('%s\n' % '\t'.join(['csaenum', 'release_state']))
                    for id, state in subject:
                        file.write('%s\n' % '\t'.join([str(id), str(state)]))

def calc_offense_coding_accuracy(**opt):
    acc = {}
    cnx = get_cnxn(opt['db_info']['name'],opt)
    c = cnx.cursor()
    c.execute("select id, abbr, label from region")
    regions = [(r[0], r[1], r[2]) for r in c.fetchall()]
    
    for seg in [('arrest', 'a', '='), ('sentence', 'c', '='),
                ('arrest', 'a', '!='), ('sentence', 'c', '!=')]:
        
        acc[seg[0]] = {}
        _key = 'WVFBI' if seg[2] == '=' else 'non wvfbi'
        acc['%s(%s)' % (seg[0], _key)] = {}
        
        #get the denominator
        c.execute("select count(*) from %s" % seg[0])
        total = float(c.fetchall()[0][0])
        
        #accuracy for all cases
        c.execute("""SELECT count(*) 
                     FROM %s 
                     WHERE %sncic is Null or %sncic = -9999""" % (seg[0], seg[1], seg[1]))
        
        total_unknown = float(c.fetchall()[0][0])
        
        acc[seg[0]]['total w/WVFBI'] = (round(100.00 - (total_unknown / total)*100, 2), total_unknown, total)
        
        #get the denominator
        c.execute("select count(*) from %s" % seg[0])
        total = float(c.fetchall()[0][0])
        
        #accuracy for all cases
        c.execute("""SELECT count(*) 
                     FROM %s 
                     INNER JOIN %sx
                     ON %s.id = %sx.id
                     WHERE (%sncic is Null or %sncic = -9999) and rapstatex %s 'wvfbi'""" % (
                     seg[0],
                     seg[0],
                     seg[0], seg[0],
                     seg[1], seg[1], seg[2])
                 )
        
        total_unknown = float(c.fetchall()[0][0])
        
        key = 'total WVFBI' if seg[2] == '=' else 'total non wvfbi'
        acc[seg[0]][key] = (round(100.00 - (total_unknown / total)*100, 2), total_unknown, total)
        
        #get accuracy by region
        for region in regions:
            c.execute("""SELECT count(*) 
                         FROM %s 
                         WHERE %sstate = %i""" % (
                         seg[0], 
                         seg[1], region[0])
                     )
            
            region_total = float(c.fetchall()[0][0])
        
            c.execute("""SELECT count(*) 
                         FROM %s 
                         WHERE %sstate = %i and (%sncic is Null or %sncic = -9999)""" % (
                         seg[0], 
                         seg[1], region[0], seg[1], seg[1]))
            
            total_region_unknown = float(c.fetchall()[0][0])
            if region_total:
                _calc = round(100.0 - (total_region_unknown / region_total)*100, 2)
            else:
                _calc = 0
            acc[seg[0]]['%s(%s)' % (region[2], region[1])] = (_calc, total_region_unknown, region_total)
        
        #get accuracy by region
        for region in regions:
            c.execute("""SELECT count(*) 
                         FROM %s 
                         INNER JOIN %sx
                         ON %s.id = %sx.id
                         WHERE %s.%sstate = %i and rapstatex %s 'wvfbi'
                         """ % (
                         seg[0], 
                         seg[0],
                         seg[0], seg[0],
                         seg[0], seg[1], region[0], seg[2])
                     )
            
            region_total = float(c.fetchall()[0][0])
        
            c.execute("""SELECT count(*) 
                         FROM %s 
                         INNER JOIN %sx
                         ON %s.id = %sx.id
                         WHERE %s.%sstate = %i and (%sncic is Null or %sncic = -9999) and rapstatex %s 'wvfbi'
                         """ % (
                         seg[0], 
                         seg[0],
                         seg[0], seg[0],
                         seg[0], seg[1], region[0], seg[1], seg[1], seg[2])
                      )
            
            total_region_unknown = float(c.fetchall()[0][0])
            if region_total:
                _calc = round(100.0 - (total_region_unknown / region_total)*100, 2)
            else:
                _calc = 0
            
            acc['%s(%s)' % (seg[0], _key)]['%s(%s)' % (region[2], region[1])] = (_calc, total_region_unknown, region_total)
    
    
    path = os.path.join(config.tmp_out_dir, 'offense_coding_accuracy_report.xls')
    if os.path.isfile(path):
        os.remove(path)
    for k,v in acc.iteritems():
        ds = cs.Dataset(['state', ('accuracy', 'float'), ('no hit count', 'integer'), ('total cases', 'integer')])
        for state, acc in v.iteritems():
            ds.add_row([state, acc[0], int(acc[1]), int(acc[2])])
            #print '%s\t%s\t%s\t%s' % (state, acc[0], acc[1], acc[2])
        ds.save(source=path, format='excel', dataset=k, overwrite=True)
        cs.wait()
    #print acc
    
    c.close()
    cnx.close()
                        
def recid_diff_report(**opt):
    ds = cs.Dataset('id')
    with closing(get_cnxn(opt['db_info']['name'],opt)) as cnx:
        with closing(cnx.cursor()) as c:
            c.execute("""select id from subject""")
            
            for subject in c.fetchall():
                c.execute("""select count(*) from arrest where casenum = %i """ % subject[0])
                stand_case_count = c.fetchall()[0][0]
                c.execute("""select count(*) from prd_cchr_recid.arrest where casenum = %i """ % subject[0])
                recid_case_count = c.fetchall()[0][0]
                
                with closing(open('output/stand_recid_diff.txt', 'a')) as file:
                    if stand_case_count != recid_case_count:
                        file.write('%s\n' % '\t'.join([str(subject[0]), str(stand_case_count), str(recid_case_count)]))
            

def handle_missing_subject_ids(method, **opt):
    missing_ids = set()
    with closing(get_cnxn(opt['db_info']['name'],opt)) as cnx:
        with closing(cnx.cursor()) as c:
            for s in ['arrest', 'sentence','demographic', 'supervision']:
                c.execute("""select distinct(casenumx) from %sx where CONVERT(CONVERT(casenumx, decimal), unsigned integer) not in (select id from subject)""" % s)
                
                missing_ids.update([int(float(id[0])) for id in c.fetchall()])
            
            print len(missing_ids)
            
            for id in missing_ids:
                if method == 'drop':
                    for s in ['arrest', 'sentence','demographic', 'supervision']:
                        c.execute('''DELETE FROM %sx 
                                     WHERE casenumx = "%s.0" or casenumx = "%s"''' % (s, id, id)
                                  )
                elif method == 'add':
                    c.execute('''INSERT INTO SUBJECT 
                                 VALUES (%i, null,null,null,null,null,null, null)''' % id)
        cnx.commit()

def drop_ids(**opt):
    missing_ids = set()
    with closing(get_cnxn(opt['db_info']['name'],opt)) as cnx:
        with closing(cnx.cursor()) as c:
            for s in ['arrest', 'sentence',]:#'demographic', 'supervision']:
                c.execute("""select distinct(casenumx) from %sx where CONVERT(casenumx, unsigned integer) not in (select id from subject)""" % s)
                
                missing_ids.update([int(id[0]) for id in c.fetchall()])
            
            for id in missing_ids:
                c.execute('''INSERT INTO SUBJECT VALUES (%i, null,null,null,null,null,null, null)''' % id)
        cnx.commit()
            
            

opt = {'db_info': db_info['stand']}
#count_states(**opt)
#count_missing(**opt)     
#recid_diff_report(**opt)
calc_offense_coding_accuracy(**opt)
#handle_missing_subject_ids('drop', **opt)