import os, re, sys
from collections import defaultdict, OrderedDict

from .configuration import *


__all__ = ['OffenseMetadata', 'AdmrecMetadata', 'load_json']

class OffenseMetadata(object):
    def __init__(self, var, phase, state):
        self.offense_map = []

        for file in os.listdir(os.path.join(config.offense_dir, phase, var, state)):
            if file.startswith('crosswalk_pred'):
                continue
            
            elif re.search('crosswalk_%s' % state, file):
                remove = '_remove' if re.search('remove', file) else ''
                rule_info = re.search('crosswalk_%s_(\d+)%s' % (state, remove),  file)
                if rule_info:
                    order = rule_info.groups()[0]
                    self.offense_map.append('%s_%s%s' % (state, order, remove))

        if len(self.offense_map) > 1:
            self.offense_map = sorted(self.offense_map, key=lambda x: int(x.split('_')[1]))



class AdmrecMetadata(object):
    def __init__(self, var, phase, region):
        self.admrec_map = defaultdict(list)
        
        _path = os.path.join(config.admrec_dir, phase, var, region) if region else os.path.join(config.admrec_dir, phase, var)   
        for file in os.listdir(_path):
            if re.search('crosswalk', file):
                rule_info = re.search('crosswalk_(.+?)_(\d+)', file)
                if rule_info:
                    state = rule_info.groups()[0]
                    order = rule_info.groups()[1]
                    self.admrec_map[state].append('%s_%s' % (state, order))

        for state, rules in self.admrec_map.iteritems():
            if len(rules) > 1:
                self.admrec_map[state] = sorted(rules, key=lambda x: int(x.split('_')[1]))

def _get_lookup(fk, phase):
    from MySQLdb import connect
    from .configuration import db_info, config
    
    db_info = db_info[phase]
    
    db = 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)
    c = db.cursor()
    c.execute('SELECT id, label FROM %s' % fk)
    _r = c.fetchall()
    c.close()
    return [[i[0], i[1].strip() if i[1] else None] for i in _r]
 
def load_json(src, segment, verbose=False):
    import json, re
    import cardsharp as cs
    from xlrd.biffh import XLRDError
    
    for file in os.listdir(src):
        if 'json' in file or os.path.isdir(os.path.join(src, file)): continue
        phase = 'recid' if re.search('recid', file) or (segment == 'demographic' and file == 'flat.xls') else 'stand' 
        _path = os.path.join(src, file)
        #load the metadata info from the user interface (excel)
        try:
            print str(_path)
            ds = cs.load(source=_path, format='excel', dataset='vars')
        except XLRDError as e:
            print e
            print 'Error XXX: Make sure that you have you the var_info.xls workbook closed. Make sure there is a worksheet named vars in var_info.xls workbook'
            sys.exit(1)
            
        ds.variables['index'].convert('integer')
        ds.variables['length'].convert('integer')
        ds.variables['precision'].convert('integer')
        if 'original' in ds.variables: 
            ds.variables['original'].convert('integer' if segment != 'subject' else 'boolean')
        if 'new' in ds.variables: 
            ds.variables['new'].convert('boolean')
              
        j = OrderedDict()
            
        for row in ds:
            print row
            row['name'] = row['name'].lower().strip()
            j[row['name']] = {}
            
            for var in ds.variables:
                
                if var.name == 'name': continue
                elif var.name == 'value_labels':
                    if not row['value_labels']:
                        j[row['name']][var.name] = None
                    elif row['value_labels'].lower() in ('none', 'null'):
                        j[row['name']][var.name] = None
                    elif row['value_labels'] == 'USE FK':
                        #TODO transform null values into string or int based on variable type
                        j[row['name']][var.name] = _get_lookup(row['fk'], phase)
                    else:
                        if verbose:
                            print row
                        j[row['name']][var.name] = json.loads(row['value_labels'])
                else:      
                    j[row['name']][var.name] = row[var.name]
        
        file = open(re.sub('xls|xlsx', 'json', _path), 'wb')
        opt = {'indent':4, 'separators':(',', ':')} if verbose else {}
        
        file.write(json.dumps(j, **opt))
        file.close()

def create_segment_metadata(meta_dir, verbose=False):
    for dir in os.listdir(meta_dir):
        if dir == 'database': continue
        load_json(os.path.join(meta_dir,dir), dir)