import re

from django.db.transaction import set_autocommit, rollback, commit

from account.models import City, School, Tag, Profile, ProfileHighSchoolInfo, \
    ProfileCollegeInfo, ProfileTagVote, ContactRecord


def bulk_create(file):
    from openpyxl import load_workbook
    
    wb = load_workbook(file, use_iterators = True)
    ws = wb.worksheets[0]
    row_counter = 1
    
    city_dict = dict([(city.name, city) for city in City.objects.all()])
    high_school_dict = dict([(s.name, s) for s in School.objects.filter(type=School.HIGH_SCHOOL_TYPE_INDEX)])
    college_dict = dict([(s.name, s) for s in School.objects.filter(type=School.COLLEGE_TYPE_INDEX)])
    tag_dict = dict([(t.name, t) for t in Tag.objects.all()])
    num_extract_regex = re.compile('(?<=[^\d])\d+(?=[^\d])')
    mobile_profile_dict = {}
    index_profile_dict = {}
    raw_encrypted_dict = {}
    def set_mobile_field(profile, raw_mobile_num, mobile_field_name): 
        if raw_mobile_num:
            profile.set_mobile(raw_mobile_num, mobile_field_name)
            raw_encrypted_dict[raw_mobile_num] = getattr(profile, mobile_field_name)
    
    def save_profile_dict(profile, mobile_field_name, mobile_num):
        if getattr(profile, mobile_field_name):
            mobile_profile_dict[mobile_num] = profile

    set_autocommit(False)
    for row in ws.iter_rows(row_offset=1): # it brings a new method: iter_rows()
        row_counter = row_counter + 1
        try:
            p = Profile()
            set_mobile_field(p, row[0].value, Profile.MOBBILE0_FIELD)
            set_mobile_field(p, row[1].value, Profile.MOBBILE1_FIELD)
            set_mobile_field(p, row[2].value, Profile.MOBBILE2_FIELD)
            p.date_of_birth = row[3].value
            p.save()
            save_profile_dict(p, Profile.MOBBILE0_FIELD, row[0].value)
            save_profile_dict(p, Profile.MOBBILE1_FIELD, row[1].value)
            save_profile_dict(p, Profile.MOBBILE2_FIELD, row[2].value)
            index_profile_dict[row_counter] = p
            if city_dict.get(row[4].value, None):
                p.cities.add(city_dict.get(row[4].value))
            if city_dict.get(row[5].value, None):
                p.cities.add(city_dict.get(row[5].value))
            if high_school_dict.get(row[6].value, None):
                kwarg = {
                    'profile': p,
                    'school': high_school_dict.get(row[6].value),
                    'date_joined': row[7].value,
                    'date_graduated': row[8].value,
                }
                phsi = ProfileHighSchoolInfo(**kwarg)
                phsi.save()
            if high_school_dict.get(row[9].value, None):
                kwarg = {
                    'profile': p,
                    'school': high_school_dict.get(row[9].value),
                    'date_joined': row[10].value,
                    'date_graduated': row[11].value,
                }
                phsi = ProfileHighSchoolInfo(**kwarg)
                phsi.save()
            if college_dict.get(row[12].value, None):
                kwarg = {
                    'profile': p,
                    'school': college_dict.get(row[12].value),
                    'date_joined': row[13].value,
                    'date_graduated': row[14].value,
                }
                pci = ProfileCollegeInfo(**kwarg)
                pci.save()
            if college_dict.get(row[15].value, None):
                kwarg = {
                    'profile': p,
                    'school': college_dict.get(row[15].value),
                    'date_joined': row[16].value,
                    'date_graduated': row[17].value,
                }
                pci = ProfileCollegeInfo(**kwarg)
                pci.save()
            if college_dict.get(row[18].value, None):
                kwarg = {
                    'profile': p,
                    'school': college_dict.get(row[18].value),
                    'date_joined': row[19].value,
                    'date_graduated': row[20].value,
                }
                pci = ProfileCollegeInfo(**kwarg)
                pci.save()
            
            tags = re.split('[,锛� ]+', row[21].value)
            for tag in tags:
                tag = tag.strip()
                if not tag:
                    continue
                vote = num_extract_regex.findall(tag)
                vote = int(vote[0]) if len(vote) else 0
                tag = re.split('[\(锛� ]+', tag)[0]
                if tag_dict.get(tag, None):
                    ptv = ProfileTagVote(profile=p, tag=tag_dict.get(tag), count=vote)
                    ptv.save()
        except Exception as e:
            e.row_counter = row_counter
            rollback()
            raise e
        
    row_counter = 1
    for row in ws.iter_rows(row_offset=1): # it brings a new method: iter_rows()
        row_counter = row_counter + 1
        try:
            mobile_nums = re.split('[,锛� ]+', row[22].value)
            for mobile_num in mobile_nums:
                mobile_num = mobile_num.strip()
                if mobile_profile_dict.get(mobile_num):
                    ContactRecord(from_profile=index_profile_dict.get(row_counter),
                                  to_profile=mobile_profile_dict.get(mobile_num),
                                  encrypted_mobile=raw_encrypted_dict.get(mobile_num)).save()
        except Exception as e:
            e.row_counter = row_counter
            rollback()
            raise e
    
    commit()
    return row_counter
