import shutil
import sys, os, re, pandas, numpy
from itertools import izip
import cardsharp as cs
import MySQLdb
from contextlib import closing
from collections import defaultdict
from itertools import count, izip

from xlrd.xldate import *

from util import key_as_str
from configuration import config, db_info

def _reverse_file(path):
    print 'reversing file...'
    with closing(open(path, 'rb')) as f:
        lines = f.readlines()
    
    with closing(open(path, 'wb')) as f:
        lines.reverse()
        f.writelines([l for l in lines[:-1]])
    
    print 'reverse complete.'
                        
def make_snttext_wvfbi_xwalk(in_path = os.path.join(config.rule_dir, 'sentence_crosswalks', 'wvfbi'), 
                             out_path = os.path.join(config.rule_dir, 'process_rules_2', 'sentence', 'snttextx', 'wvfbi')):
    
    in_ds = cs.load(source=os.path.join(in_path, '2_or_more.xls'), format = 'excel')
    out_ds = cs.Dataset(['key', 'value', ('priority', 'integer')])
    in_ds.variables['value:cdsp'].convert('integer')
    in_ds.variables['value:cnf'].convert('integer')
    
    out_ds.add_row([':vars:','cdsp|cnf|incmax|incmin|suspmax|suspmin|prb'])
    out_ds.add_row([':keys:','cdisptextx'])
    out_ds.add_row([':lookup:','dsp|cnf'])
    
    for _c, row in izip(count(1), in_ds):
        if row['key:cdisptextx'] is None:
            print _c
        else:
            key = row['key:cdisptextx'].lower().strip()
        out = '|'.join([str(row['value:cdsp'])if row['value:cdsp'] else '', 
                        str(row['value:cnf']) if row['value:cnf'] else '', 
                        row['value:incmax'] or '', 
                        row['value:incmin'] or '', 
                        row['value:suspmax'] or '', 
                        row['value:suspmin'] or '', 
                        row['value:prb'] or '']).replace('=', '')
        
        if '#' in key or '#' in out or '(' in out:
            in_val = ''.join([':re:math-e:', key])
        elif '#' in row['key:cdisptextx']:
            in_val = ''.join([':re:exact:', key])
        else:
            in_val = key
    
        out_ds.add_row([in_val, out, _c])
    
    out_ds.save(source=os.path.join(out_path, 'crosswalk_2_or_more.txt'), format='text', overwrite=True, no_escape=True)
    cs.wait()
    _reverse_file(os.path.join(out_path, 'crosswalk_2_or_more.txt'))

    print '...2_or_more complete...'
    print '...loading single...'
    output_path = os.path.join(out_path, 'crosswalk_single.txt')
    output = open(output_path, "wb")

    output.write(':vars:\tcdsp|pjp\n')
    output.write(':keys:\tcdisptextx\n')
    output.write(':lookup:\tdsp|pjp\n')
    
    workbook = pandas.read_excel(os.path.join(in_path, 'single.xls'), sheetname=None)
    sheet_exists, sheet_num = True, 1
    while sheet_exists:
        try:
            sheet = workbook["crosswalk_%s" % sheet_num]
            
            for index, row in sheet.iterrows():
                key = str(row['key:cdisptextx']).lower().strip()
                value = '|'.join([str(row['value:cdsp']), str(row['value:pjp'])])
                output.write("%s\t%s\n" % (key, value,))

            sheet_num += 1
        except KeyError:
            sheet_exists = False
    output.close()
    print 'single complete...'


def _get_tables():
    with closing(MySQLdb.connect(user=db_info['stand']['user'], passwd=db_info['stand']['pass'])) as cnx:
        with closing(cnx.cursor()) as c:
            c.execute('SHOW TABLES FROM %s' % config.db_stand_name)
            
            tbl_names = [table[0] for table in c.fetchall()]
    
    return tbl_names

_in_key_re = re.compile('(?<=key:).+?(?=($|\())')
_out_var_re = re.compile('value:|\(skip\)')


def _get_out_vars(ds):
    return [_out_var_re.sub('', v.name) for v in ds.variables if v.name.startswith('value:')]


def _get_in_keys(ds):
    return [_in_key_re.search(v.name).group() for v in ds.variables if v.name.startswith('key:')]


def _convert_out_vals(in_ds, lookup):
    #TODO we need to be able to load math rules
    skip_convert = False
    for row in in_ds:
        if row[0] == ':all:':
            return
        
    for var in in_ds.variables:
        if var.name.startswith('value:'):
            if var.name.replace('value:', '')[1:] in lookup or var.name.replace('value:','') in lookup:
                in_ds.variables[var.name].convert('integer')

# function to replace .0 in string values (excel load issue)
regex = re.compile('^\d+?\.0$')
def _re_replace(value, no_replace):
    if no_replace:
        return value.replace('(blank-space)', ' ')
    if regex.match(value):
        #print 'replacing .0'
        value = value.replace('.0', '')
        
        #replace (blank-space) with ' '
        return value.replace('(blank-space)', ' ')
    else:
        return value.replace('(blank-space)', ' ')
                
_loc_dict = {('state', 'achgsvr'): lambda state: '/process_rules_1/arrest/achgsvr/crosswalk_%s.txt' % state,
             ('state', 'cchgsvr'): lambda state: '/process_rules_1/sentence/cchgsvr/crosswalk_%s.txt' % state,
             ('state', 'pchgsvr'): lambda state: '/process_rules_1/prosecution/pchgsvr/crosswalk_%s.txt' % state,
             ('state', 'ancic'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_%s_1.txt' % (state, state),
             ('state', 'cncic'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_%s_1.txt' % (state, state),
             ('state', 'pncic'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_%s_1.txt' % (state, state),
             ('state', 'ainc'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'cinc'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'pinc'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'ainc1'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'cinc1'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'pinc1'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_inc_1.txt' % state,
             ('state', 'acdv'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_cdv_1.txt' % state,
             ('state', 'ccdv'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_cdv_1.txt' % state,
             ('state', 'pcdv'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_cdv_1.txt' % state,
             ('state', 'ainc2'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_inc_2.txt' % state,
             ('state', 'cinc2'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_inc_2.txt' % state,
             ('state', 'pinc2'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_inc_2.txt' % state,
             ('state', 'adom'): lambda state: '/process_rules_2/arrest/ancic/%s/crosswalk_dom_1.txt' % state,
             ('state', 'cdom'): lambda state: '/process_rules_2/sentence/cncic/%s/crosswalk_dom_1.txt' % state,
             ('state', 'pdom'): lambda state: '/process_rules_2/prosecution/pncic/%s/crosswalk_dom_1.txt' % state,
             ('wvfbi', 'ancic'): lambda state: '/process_rules_2/arrest/ancic/wvfbi/crosswalk_%s.txt' % state,
             ('wvfbi', 'cncic'): lambda state: '/process_rules_2/sentence/cncic/wvfbi/crosswalk_%s.txt' % state,
             ('wvfbi', 'pncic'): lambda state: '/process_rules_2/prosecution/pncic/wvfbi/crosswalk_%s.txt' % state,
             ('state', 'nonarr'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_1.txt' % state,
             ('state', 'nonarr1'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_1.txt' % state,
             ('state', 'nonarr2'): lambda state: '/process_rules_3/arrest/nonarr/crosswalk_%s_2.txt' % state,
             ('state', 'noncrt'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_1.txt' % state,
             ('state', 'noncrt1'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_1.txt' % state,
             ('state', 'noncrt2'): lambda state: '/process_rules_3/sentence/noncrt/crosswalk_%s_2.txt' % state,
             ('state', 'nonprs'): lambda state: '/process_rules_3/prosecution/nonprs/crosswalk_%s_1.txt' % state,
             ('state', 'nonprs1'): lambda state: '/process_rules_3/prosecution/nonprs/crosswalk_%s_1.txt' % state,
             ('state', 'nonprs2'): lambda state: '/process_rules_3/prosecution/nonprs/crosswalk_%s_2.txt' % state,
             ('wvfbi', 'nonarr'): lambda state: '/process_rules_3/arrest/nonarr/wvfbi/crosswalk_%s_1.txt' % state,
             ('wvfbi', 'noncrt'): lambda state: '/process_rules_3/sentence/noncrt/wvfbi/crosswalk_%s_1.txt' % state,
             ('wvfbi', 'nonprs'): lambda state: '/process_rules_3/prosecution/nonprs/wvfbi/crosswalk_%s_1.txt' % state,
             ('state', 'adsp'): lambda state: '/process_rules_2/arrest/adsp/crosswalk_%s.txt' % state,
             ('state', 'cdsp'): lambda state: '/process_rules_2/sentence/cdsp/crosswalk_%s.txt' % state,
             ('state', 'pdsp'): lambda state: '/process_rules_2/prosecution/pdsp/crosswalk_%s.txt' % state,
             ('state', 'acnt'): lambda state: '/process_rules_2/arrest/acnt/%s/crosswalk_%s.txt' % (state, state),
             ('state', 'ccnt'): lambda state: '/process_rules_2/sentence/ccnt/%s/crosswalk_%s.txt' % (state, state),
             ('state', 'pcnt'): lambda state: '/process_rules_2/prosecution/pcnt/%s/crosswalk_%s.txt' % (state, state),
             ('wvfbi', 'adsp'): lambda state: '/process_rules_2/arrest/adsp/wvfbi/crosswalk_%s.txt' % state,
             ('wvfbi', 'cdsp'): lambda state: '/process_rules_2/sentence/cdsp/wvfbi/crosswalk_%s.txt' % state,
             ('wvfbi', 'pdsp'): lambda state: '/process_rules_2/prosecution/pdsp/wvfbi/crosswalk_%s.txt' % state,
            }

#add cdsp locations
for i in xrange(1, 100):
    _loc_dict[('state', 'cdsp%i' % i)] = lambda args: '/process_rules_2/sentence/cdsp/%s/crosswalk_%s_%i.txt' % (args[1], args[1], args[0])
    _loc_dict[('state', 'pdsp%i' % i)] = lambda args: '/process_rules_2/prosecution/pdsp/%s/crosswalk_%s_%i.txt' % (args[1], args[1], args[0])
    
    
def load_all_crosswalk(input_file, output_path, type, no_replace):
    # get tables to construct lookup rule
    tbl_names = _get_tables()

    state = re.search('(state|wvfbi)\\\\(.+).xls', input_file).groups(1)[1]
    
    # create one crosswalk for each worksheet in xwalk dataset
    for ds in cs.list_datasets(source=input_file):
        # load dataset
        in_ds = cs.load(source=input_file, format='excel', dataset=ds)
        out_ds_1 = cs.Dataset(['key', 'value', 'priority'])
        out_ds_2 = cs.Dataset(['key', 'value', 'priority'])
        out_ds_3 = cs.Dataset(['key', 'value', 'priority'])

        #get the output vars
        out_vars = _get_out_vars(in_ds)
        in_key = _get_in_keys(in_ds)
        lookup = []
        priority  = ''
        offense_ds = re.search('offense', ds)
        # create :lookup: rule
        for v in out_vars:
            if v[1:] in tbl_names or (re.search('offense', ds) and v in tbl_names) or v in ['nonarr', 'noncrt', 'nonprs']:
                if v[1:] not in ['nonarr', 'noncrt', 'nonprs'] and v not in ['nonarr', 'noncrt', 'nonprs']:
                    l_v = v
                elif re.search('offense|ancic|cncic|pncic', ds):
                    l_v = v  
                else:
                    l_v = 'admrec'
            
                lookup.append(l_v[1:] if not re.search('offense|nonarr|noncrt|nonprs', ds) else l_v) #assign admrec if not an offense crosswalk #todo this is dirty fix this
        
        #convert to output values to integers
        _convert_out_vals(in_ds, lookup)
        in_ds.wait() 
        
        lookup = [':lookup:',  '|'.join(lookup)] if lookup else None
        
            
        #create the :vars: rule
        out_vars_rule = [':vars:',  '|'.join(out_vars)] if out_vars else None
        out_vars_rule_a = [':vars:',  '|'.join(['a%s' % v for v in out_vars])] if out_vars else None
        out_vars_rule_s = [':vars:',  '|'.join(['c%s' % v for v in out_vars])] if out_vars else None
        out_vars_rule_p = [':vars:',  '|'.join(['p%s' % v for v in out_vars])] if out_vars else None
        
        #create the :keys: rule
        in_keys_rule = [':keys:',  '|'.join(in_key)]
        in_keys_rule_a = [':keys:',  '|'.join(['a%s' % v for v in in_key])]
        in_keys_rule_s = [':keys:',  '|'.join(['c%s' % v for v in in_key])]
        in_keys_rule_p = [':keys:',  '|'.join(['p%s' % v for v in in_key])]
        
        # create the skip_missing rule
        skip_missing = []
        
        for v in in_ds.variables:
            if re.search('(skip)', v.name):
                skip_missing.append(re.sub('value:|\(skip\)', '', v.name))
        
        # create the key transform rule
        transform = []
        
        for v in in_ds.variables:
            if re.search('replace|sub', v.name):
                v_name = _in_key_re.search(v.name).group()
                t = re.search('(replace|sub):(.+?):(.+?)(?<!\\\\)\)', v.name).groups()
                transform.append((v_name, t[0], t[1], t[2]))
        
        # create the :skip_missing: rule
        skip_rule = [':skip_missing:', '|'.join(skip_missing)]
        skip_rule_a = [':skip_missing:',  '|'.join(['a%s' % v for v in skip_missing])]
        skip_rule_s = [':skip_missing:',  '|'.join(['c%s' % v for v in skip_missing])]
        skip_rule_p = [':skip_missing:',  '|'.join(['p%s' % v for v in skip_missing])]
        
        # create the :key: rules
        transform_rules, transform_rules_a, transform_rules_s, transform_rules_p = [], [], [], []
        for t in transform:
            transform_rules.append([':key:\t%s\t:%s:\t%s' % (t[0], t[1], t[2]), t[3]])
            transform_rules_a.append([':key:\ta%s\t:%s:\t%s' % (t[0], t[1], t[2]), t[3]])
            transform_rules_s.append([':key:\tc%s\t:%s:\t%s' % (t[0], t[1], t[2]), t[3]])
            transform_rules_p.append([':key:\tp%s\t:%s:\t%s' % (t[0], t[1], t[2]), t[3]])
        
        # add the :vars: and :keys: and :lookup: rules to the dataset
        if re.search('offense', ds):
            # for offesne rules we use one crosswalk to output to 3 segements
            if out_vars_rule_a and not re.search('none', ''.join(out_vars_rule_a)):#make sure we do not have none for a key (delete crosswalk)
                out_ds_1.add_row(out_vars_rule_a)
                out_ds_2.add_row(out_vars_rule_s)
                out_ds_3.add_row(out_vars_rule_p)

            out_ds_1.add_row(in_keys_rule_a)
            out_ds_2.add_row(in_keys_rule_s)
            out_ds_3.add_row(in_keys_rule_p)

            if skip_missing:
                out_ds_1.add_row(skip_rule_a)
                out_ds_2.add_row(skip_rule_s)
                out_ds_3.add_row(skip_rule_p)
            
            if transform_rules_a:
                for t in transform_rules_a:
                    out_ds_1.add_row(t)
                for t in transform_rules_s:
                    out_ds_2.add_row(t)
                for t in transform_rules_p:
                    out_ds_3.add_row(t)
            
            if lookup:    
                out_ds_2.add_row(lookup)
                out_ds_3.add_row(lookup)
        
        else:
            if out_vars_rule and not re.search('none', ''.join(out_vars_rule)): # make sure we do not have none for a key (delete crosswalk)
                out_ds_1.add_row(out_vars_rule)
            out_ds_1.add_row(in_keys_rule)

            if skip_missing:
                out_ds_1.add_row(skip_rule)
            
            if transform_rules:
                for t in transform_rules:
                    out_ds_1.add_row(t)
        
        if lookup:
            out_ds_1.add_row(lookup)
            
        #TODO figure out which variables are integers
        for row in in_ds:
            key = []
            sentence_key = []
            prosecution_key = []

            value = []
            #iterate over variables to gather key : value ruel mapping
            _rule_type = ''
            for var in in_ds.variables:
                var_name = re.sub('value:|key:', '', var.name).lower()
                _var = row.get(var.name)
                
                #do not add (blank) rule 
                if _var == '(blank)':
                    continue
                
                #create key
                
                if var_name not in out_vars and var_name != 'priority' and not re.search('skip', var_name) and not var_name.startswith('pii'):
                    if var_name == 'rule_type': 
                        if row['rule_type'] is not None:
                            _rule_type = row['rule_type']
                        continue
                        
                    if _rule_type:    
                        key_value = '%s%s' % (_rule_type, key_as_str(row[var.name]))
                        _rule_type = ''
                    else:
                        key_value = key_as_str(row[var.name])
                    
                    #this is deprecated remove in future
                    if '(re)' in var_name and '#' in key_value:
                        key_value = ':re:search:%s' % key_value
                        
                    #if (c) in key only add to sentence_key    
                    if '(c)' in var_name:
                        sentence_key.append(_re_replace(key_value, no_replace))
                    if '(p)' in var_name:
                        prosecution_key.append(_re_replace(key_value, no_replace))
                    #otherwise update both keys
                    else:
                        key.append(_re_replace(key_value, no_replace))
                        sentence_key.append(_re_replace(key_value, no_replace))
                        prosecution_key.append(_re_replace(key_value, no_replace))
                
                #create vlaue
                elif var_name != 'priority' and not var_name.startswith('pii'):
                    value.append(_re_replace(str(row[var.name]), False) if row[var.name] is not None else '')
                
                priority = str(row[var_name]) if (var_name == 'priority' and row[var_name]) else '1'

            if key:
                out_ds_1.add_row(['|'.join(key), '|'.join(value), priority])
                if offense_ds:
                    out_ds_2.add_row(['|'.join(sentence_key), '|'.join(value), priority])
                    out_ds_3.add_row(['|'.join(prosecution_key), '|'.join(value), priority])

        # save the datasets
        if offense_ds:
            
            offense_num = re.search('offense(\d+)', ds)
            offense_num = offense_num.groups()[0] if offense_num else ''
            
            if offense_num:
                _loc_dict[('state', 'ancic%s' % offense_num)] = lambda info: '/process_rules_2/arrest/ancic/%s/crosswalk_%s_%s.txt' % (info[0], info[0], info[1])
                _loc_dict[('state', 'cncic%s' % offense_num)] = lambda info: '/process_rules_2/sentence/cncic/%s/crosswalk_%s_%s.txt' % (info[0], info[0], info[1])
                _loc_dict[('state', 'pncic%s' % offense_num)] = lambda info: '/process_rules_2/prosecution/pncic/%s/crosswalk_%s_%s.txt' % (info[0], info[0], info[1])
                _loc_dict[('wvfbi', 'ancic%s' % offense_num)] = lambda info: '/process_rules_2/arrest/ancic/wvfbi/crosswalk_%s.txt' % info[0]
                _loc_dict[('wvfbi', 'cncic%s' % offense_num)] = lambda info: '/process_rules_2/sentence/cncic/wvfbi/crosswalk_%s.txt' % info[0]
                _loc_dict[('wvfbi', 'pncic%s' % offense_num)] = lambda info: '/process_rules_2/prosecution/pncic/wvfbi/crosswalk_%s.txt' % info[0]
             
            
            for k,v in [(out_ds_1, 'ancic.txt'), (out_ds_2, 'cncic.txt'), (out_ds_3, 'pncic.txt')]:
                k.variables['priority'].convert('integer')
                
                if offense_num:
                    temp_out = config.rule_dir + _loc_dict[(type, '%s%s' %(v[:5], offense_num))]((state, offense_num if not re.search('remove', ds) else '%s_remove' % offense_num))
                else:
                    temp_out = config.rule_dir + _loc_dict[(type, v[:5])](state)
                if not temp_out:
                    temp_out = os.path.join(output_path, v)
                    
                k.save(source=temp_out, format='text', overwrite=True, no_escape=True)
                
                cs.wait()
                _reverse_file(temp_out)
                
                #handle the ne/nb state
                if state == 'ne':
                    temp_out = temp_out.replace('/ne/', '/nb/').replace('_ne_', '_nb_')
                    k.save(source=temp_out, format='text', overwrite=True, no_escape=True)
                    cs.wait()
                    _reverse_file(temp_out)

        else:
            #handle cdsp cases
            _c = re.search('cdsp(\d+)', ds)
            _p = re.search('pdsp(\d+)', ds)

            if 'cdsp' in ds and _c:
                temp_out = _loc_dict.get((type, ds), lambda s: False)((int(_c.groups()[0]), state))
                if temp_out and not os.path.isfile(config.rule_dir + temp_out):
                    print 'adding new state cdsp crosswalk. Makes sure to update state cdsp rule controller.'
            elif 'pdsp' in ds and _p:
                temp_out = _loc_dict.get((type, ds), lambda s: False)((int(_p.groups()[0]), state))
                if temp_out and not os.path.isfile(config.rule_dir + temp_out):
                    print 'adding new state pdsp crosswalk. Makes sure to update state pdsp rule controller.'
            else:
                temp_out = _loc_dict.get((type, ds), lambda s: False)(state)
                
            if not temp_out:
                temp_out = os.path.join(output_path, '%s.txt' % ds)
            else:
                temp_out = config.rule_dir + temp_out
                
            out_ds_1.variables['priority'].convert('integer')
            out_ds_1.save(source=temp_out, format='text', overwrite=True, no_escape=True)
            cs.wait()
            _reverse_file(temp_out)
            
            if state=='ne':
                temp_out = temp_out.replace('/ne/', '/nb/').replace('_ne_', '_nb_')
                out_ds_1.save(source=temp_out, format='text', overwrite=True, no_escape=True)
                cs.wait()
                _reverse_file(temp_out)

                
def load_offense_rules(state, no_replace):
    try:
        if state != 'fed':
            load_all_crosswalk(r'%s\offense_crosswalks\state\%s.xls' % (config.rule_dir, state),
                       config.rule_dir + '/crosswalk_raw/state/%s' % state, 'state', no_replace)
            load_all_crosswalk(r'%s\offense_crosswalks\wvfbi\%s.xls' % (config.rule_dir, state),
                       config.rule_dir + '/crosswalk_raw/wvfbi/%s' % state, 'wvfbi', no_replace)
        
        if state == 'fed':
            load_all_crosswalk(r'%s\offense_crosswalks\wvfbi\%s.xlsx' % (config.rule_dir, state),
                       config.rule_dir + '/crosswalk_raw/wvfbi/%s' % state, 'wvfbi', no_replace)
    except (XLDateAmbiguous, XLDateError), e:
        print "Unable to load. Make sure that you do not have date values stored in your excel file. Select all cells and format them as text. %s" % str(e)
        sys.exit(1)


def _load_sentence_ruleset(crosswalk, step, region, no_replace, sheet_type=''):
    _sheet_type = '%s_' % sheet_type if sheet_type else sheet_type
    _sheet_type_out = '_%s' % sheet_type if sheet_type else sheet_type

    metadata = crosswalk["%s_%smetadata" % (step, _sheet_type)].fillna("")
    rules = crosswalk["%s_%srules" % (step, _sheet_type)].fillna("")
    output_path = os.path.join(config.rule_dir, "process_rules_2/sentence/snttextx/%s/crosswalk_%s%s.txt" % (region, step, _sheet_type_out))
    
    if not no_replace or not os.path.exists(output_path):
        output = open(output_path, "w")
        for index, row in metadata.iterrows():
            output.write("%s\n" % "\t".join([str(r) for r in row if not pandas.isnull(r)]).replace("\n", ""))
        for index, row in rules.iterrows():
            output.write("%s\t%s\t%s\n" % (
                "".join([str(row[0]), str(row[1]).lower()]).replace("\n", ""),
                "|".join([str(r) for r in row[2:-1]]).replace("\n", ""),
               str(row[-1]).replace("\n", "")
            ))
        output.close()
        print "Updated and saved %s" % output_path
        
    if region == 'ne':
        output_path_nb = os.path.join(config.rule_dir, "process_rules_2/sentence/snttextx/%s/crosswalk_%s%s.txt" % ('nb', step, _sheet_type_out))    
        shutil.copyfile(output_path, output_path_nb)


def load_sentence_rules(region, no_replace):
    if region:
        if os.path.isfile(os.path.join(config.rule_dir, "sentence_crosswalks/%s/crosswalks.xls" % str(region))):
            crosswalk_excel = pandas.read_excel(os.path.join(config.rule_dir, "sentence_crosswalks/%s/crosswalks.xls" % str(region)), sheetname=None)
            steps = set([name[:-6] for name in crosswalk_excel.keys() if name.endswith('_rules')])
            for step in steps:
                try:
                    _load_sentence_ruleset(crosswalk_excel, step, region, no_replace)
                    try:
                        #try to load the optional remove rulesets
                        _load_sentence_ruleset(crosswalk_excel, step, region, no_replace, 'remove')
                    except KeyError:
                        pass
                except:
                    raise


def _move_ncic(type, region, file, path):
    if type == 'c':
        seg = 'sentence'
    elif type == 'p':
        seg = 'prosecution'
    else:
        seg = 'arrest'

    if region == 'ne':
        _file = re.sub('(a|c|p)ncic(\d+)', 'crosswalk_nb_\\2', file)
        shutil.copy(path, os.path.join(config.rule_dir, config.offense_dir, seg, '%sncic' % type, 'nb', _file))
        
    _file = re.sub('(a|c|p)ncic(\d+)', 'crosswalk_%s_\\2' % region, file)
    shutil.move(path, os.path.join(config.rule_dir, config.offense_dir, seg, '%sncic' % type, region, _file))
    
def load_rules(region, wvfbi, no_replace, sentence, offense):
    if wvfbi:
        print 'Creating wvfbi snttext crosswalks...'
        make_snttext_wvfbi_xwalk()
        print 'wvfbi snttext crosswalks written.'
    
    if region:
        _dir = os.path.join(config.rule_dir, 'crosswalk_raw')
        print 'loading %s rules' % region
        for folder in ('state', 'wvfbi'):
            _out_path = os.path.join(_dir, folder, region) 
            if not os.path.isdir(_out_path): 
                os.makedirs(_out_path)
            if region == 'ne':
                _out_path = os.path.join(_dir, folder, 'nb')
                if not os.path.isdir(_out_path):
                    os.makedirs(_out_path)
                _out_path = os.path.join(_dir, folder, 'ne')

        if not sentence:
            load_offense_rules(region, no_replace)
            print 'offense crosswalks added'
        if not offense:
            load_sentence_rules(region, no_replace)
            print "sentence crosswalks added"
        
        for folder in ('state', 'wvfbi'):    
            _out_path = os.path.join(_dir, folder, region)
            files = os.listdir(_out_path)
            
            if folder == 'state':
                for file in files:
                    if file.startswith('cncic'): 
                        _move_ncic('c', region, file, os.path.join(_out_path, file))
                    elif file.startswith('ancic'):
                        _move_ncic('a', region, file, os.path.join(_out_path, file))
                    elif file.startswith('pncic'):
                        _move_ncic('p', region, file, os.path.join(_out_path, file))
            
            if len(os.listdir(_out_path)) == 0:
                os.rmdir(_out_path)
            else:
                print 'Not all crosswalks able to save. Check crosswalk_raw folder.'
        
    cs.wait()