from openpyxl import load_workbook
from visit import models
from django.db import transaction
import datetime

from django.contrib.auth import models as authmodels
from django.db.models import Q
from django.core.mail import send_mail
from django.template.loader import render_to_string

def send_pwticket_email(ticket):
    pass

def activate_pw_ticket(user):
    ticket = models.PWTicket.objects.create(user=user)
    send_pwticket_email(ticket)

    pass

def send_submitted_visit_email(visit, only_pd=False, only_sc=False):
    if only_pd:
        recips = ['pd', ]
    elif only_sc:
        recips = ['sc', ]
    elif visit.type == 'contact_attempt':
        recips = ['staff1', 'sc', 'pd']
    else:
        recips = ['staff1', 'staff2', 'sc', 'pd']

    for recipient in recips:
        message = render_to_string('visit/visit-log-email.html', {
            'visit': visit,
            'recipient': recipient,
            })
        if recipient == 'staff1':
            to = [visit.staff1.email, ]
        elif recipient == 'staff2' and visit.staff2:
            to = [visit.staff2.email, ]
        elif recipient == 'sc':
            #q = Q(school=visit.school) | Q(secondary_schools=visit.school)
            to = [sc.staff.email for sc in models.SiteCoordinator.objects.filter(school=visit.school, staff__programdirector__isnull=True)]
        elif recipient == 'pd':
            to = [pd.staff.email for pd in models.ProgramDirector.objects.filter(schools=visit.school, receive_emails=True).distinct()]
            #to = [u.email for u in authmodels.User.objects.filter(staff__programdirector__schools=visit.school, staff__programdirector__receive_emails=True).distinct()]
            #to = [s.email for s in models.Staff.objects.filter(is_program_director=True, program_director_schools=visit.school)]
            #to = [s.email for s in visit.school.staff.filter(is_program_director=True)]
        send_mail(subject='HOME WORKS! visit report #%d' % (visit.id),
                message=message,
                from_email='portal@teacherhomevisit.org',
                recipient_list=to,
                html_message=message,
                )

def send_submitted_visit_email2(visit, only_pd=False, only_sc=False):
    if visit.type == 'complete':
        recips = ('staff1', 'staff2', 'sc', 'pd')
        if only_pd:
            recips = ('pd', )
        if only_sc:
            recips = ('sc', )
        for recipient in recips:
            message = render_to_string('visit/email-complete.html', {
                'visit': visit,
                'recipient': recipient,
                })
            if recipient == 'staff1':
                to = [visit.staff1.email, ]
            elif recipient == 'staff2':
                to = [visit.staff2.email, ]
            elif recipient == 'sc':
                #q = Q(school=visit.school) | Q(secondary_schools=visit.school)
                to = [sc.staff.email for sc in models.SiteCoordinator.objects.filter(school=visit.school)]
                #to = [s.email for s in authmodels.User.objects.filter(sitecoordinator__school=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_site_coordinator=True)]
            elif recipient == 'pd':
                to = [s.email for s in authmodels.User.objects.filter(staff__programdirector__schools=visit.school).distinct()]
                #to = [s.email for s in models.Staff.objects.filter(is_program_director=True, program_director_schools=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_program_director=True)]
            send_mail(subject='HOME WORKS! visit report #%d - Completed Visit' % (visit.id),
                    message=message,
                    from_email='portal@teacherhomevisit.org',
                    recipient_list=to,
                    html_message=message,
                    )
    elif visit.type == 'contact_attempt':
        recips = ('staff1', 'sc', 'pd')
        if only_pd:
            recips = ('pd', )
        if only_sc:
            recips = ('sc', )
        for recipient in recips:
            message = render_to_string('visit/email-contact.html', {
                'visit': visit,
                'recipient': recipient,
                })
            if recipient == 'staff1':
                to = [visit.staff1.email, ]
            elif recipient == 'sc':
                #q = Q(school=visit.school) | Q(secondary_schools=visit.school)
                #to = [s.email for s in models.Staff.objects.filter(is_site_coordinator=True).filter(q).distinct()]
                to = [sc.staff.email for sc in models.SiteCoordinator.objects.filter(school=visit.school)]
                #to = [s.email for s in authmodels.User.objects.filter(sitecoordinator__school=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_site_coordinator=True)]
            elif recipient == 'pd':
                to = [s.email for s in authmodels.User.objects.filter(staff__programdirector__schools=visit.school).distinct()]
                #to = [s.email for s in models.Staff.objects.filter(is_program_director=True, program_director_schools=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_program_director=True)]
            send_mail(subject='HOME WORKS! visit report #%d - Attempt to Contact' % (visit.id),
                    message=message,
                    from_email='portal@teacherhomevisit.org',
                    recipient_list=to,
                    html_message=message,
                    )
    elif visit.type == 'noshow':
        recips = ('staff1', 'sc', 'pd')
        if only_pd:
            recips = ('pd', )
        if only_sc:
            recips = ('sc', )
        for recipient in recips:
            message = render_to_string('visit/email-noshow.html', {
                'visit': visit,
                'recipient': recipient,
                })
            if recipient == 'staff1':
                to = [visit.staff1.email, ]
            elif recipient == 'staff2':
                to = [visit.staff2.email, ]
            elif recipient == 'sc':
                #q = Q(school=visit.school) | Q(secondary_schools=visit.school)
                #to = [s.email for s in models.Staff.objects.filter(is_site_coordinator=True).filter(q).distinct()]
                to = [sc.staff.email for sc in models.SiteCoordinator.objects.filter(school=visit.school)]
                #to = [s.email for s in authmodels.User.objects.filter(sitecoordinator__school=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_site_coordinator=True)]
            elif recipient == 'pd':
                to = [s.email for s in authmodels.User.objects.filter(programdirector__schools=visit.school)]
                #to = [s.email for s in models.Staff.objects.filter(is_program_director=True, program_director_schools=visit.school)]
                #to = [s.email for s in visit.school.staff.filter(is_program_director=True)]
            send_mail(subject='HOME WORKS! visit report #%d - No Show' % (visit.id),
                    message=message,
                    from_email='portal@teacherhomevisit.org',
                    recipient_list=to,
                    html_message=message,
                    )
    else:
        raise Exception("Visit ID %d: Unknown visit type" % visit.id)

    #message = """Here is a copy of the HOME WORKS! home visit report <strong>Entry ID: </strong>{visit.id} completed by {visit.staff1} on {visit.date_submitted}. Thank you!
    #<p><b>Student:</b> {visit.student}</p>
    #""".format(visit=visit)
    #<p><b>Caregiver:</b> {visit.caregiver}</p>
    #<p><b>Time:</b> {visit.time_begin} - {visit.time_end}</p>
    #<p><b>Student Present:</b> {visit.student_present}</p>
    #<p><b>Location:</b> {visit.location}</p>

    #message = """Here is a copy of the HOME WORKS! home visit report <strong>Entry ID: </strong>{visit.id} completed by {visit.staff1} from your school on {visit.date_submitted}. Thank you!
    #<p><b>Student:</b> {visit.student}</p>
    #""".format(visit=visit)
    #<p><b>Caregiver:</b> {visit.caregiver}</p>
    #<p><b>Time:</b> {visit.time_begin} - {visit.time_end}</p>
    #<p><b>Location:</b> {visit.location}</p>
    #<p><b>Student Present:</b> {visit.student_present}</p>

@transaction.atomic()
def import_school_workbook(fname, district_name=None):
    messages = []
    #district = models.District.objects.get_or_create(name=district_name)[0]
    #school = models.School.objects.get_or_create(name=school_name, district=district)[0]

    wb = load_workbook(filename=fname)
    ws = wb.get_sheet_by_name('Sheet1')
    #ws = wb.get_sheet_by_name('Input Data')

    staff = []

    COLS = {
            'ACTION': {'title': 'Action', 'num': None},
            'DISTRICT': {'title': 'District', 'num': None},
            'SCHOOL': {'title': 'School', 'num': None},
            'STAFF_LN': {'title': 'Teacher/Staff Last Name', 'num': None},
            'STAFF_FN': {'title': 'Teacher/Staff First Name', 'num': None},
            'STAFF_EMAIL': {'title': 'Teacher School E-mail', 'num': None},
            'STAFF_POS': {'title': 'Teacher Position', 'num': None},
            'CLASS_SZ': {'title': 'Class Size', 'num': None},
            'HW_STATUS': {'title': 'HOME WORKS! Status', 'num': None},
            'STUDENT_ID': {'title': 'Student ID', 'num': None},
            'STUDENT_LN': {'title': 'Student Last Name', 'num': None},
            'STUDENT_FN': {'title': 'Student First Name', 'num': None},
            'STUDENT_GRADE': {'title': 'Grade', 'num': None},
            'STUDENT_GENDER': {'title': 'Gender', 'num': None},
            'STUDENT_ETHNICITY': {'title': 'Std Ethnicity', 'num': None},
            'STAFF_PD1': {'title': 'PD1 Training', 'num': None},
            'STAFF_PD2': {'title': 'PD2 Training', 'num': None},
            #'STAFF_REFRESH': {'title': '14-15 Refresher Training', 'num': None},
            'STAFF_SC': {'title': 'Princ/SC - Training', 'num': None},
            }

    for x in range(1,20):
        try:
            val = ws.cell(column=x, row=1).value.strip()
        except:
            raise Exception("Column %d: Error getting column name" % (x))

        for k,v in COLS.items():
            if val == v['title']:
                v['num'] = x

    for k,v in COLS.items():
        if v['num'] is None:
            raise Exception("Error: Can't find column '%s'" % (v['title']))

    for row in range(2,ws.max_row+1):
      try:
        dis = ws.cell(column=COLS['DISTRICT']['num'], row=row).value
        if not dis:
            messages.append("Skipping row %d: no district" % (row))
            continue

        sch = ws.cell(column=COLS['SCHOOL']['num'], row=row).value
        if not sch:
            messages.append("Skipping row %d: no school" % (row))
            continue

        district = models.District.objects.get_or_create(name=dis.strip())[0]
        school = models.School.objects.get_or_create(name=sch.strip(), defaults={'district': district})[0]

        if ws.cell(column=COLS['STAFF_EMAIL']['num'], row=row).value:
            staff_fn = ws.cell(column=COLS['STAFF_FN']['num'], row=row).value
            staff_ln = ws.cell(column=COLS['STAFF_LN']['num'], row=row).value

            if not (staff_fn and staff_ln) or not (staff_fn.strip() and staff_ln.strip()):
                messages.append("Skipping row %d: incomplete staff info." % (row))
                continue

            if not '@' in ws.cell(column=COLS['STAFF_EMAIL']['num'], row=row).value:
                raise Exception("Error row %d: invalid email address" % (row))

            if ws.cell(column=COLS['STAFF_SC']['num'], row=row).value:
                is_site_coordinator = True
            else:
                is_site_coordinator = False

            status = ws.cell(column=COLS['HW_STATUS']['num'], row=row).value
            if status:
                status = status.strip()
            else:
                status = 'Unknown'

            teacher = {
                    'last_name': staff_ln.strip().title(),
                    'first_name': staff_fn.strip().title(),
                    'email': ws.cell(column=COLS['STAFF_EMAIL']['num'], row=row).value.strip().lower(),
                    'position': str(ws.cell(column=COLS['STAFF_POS']['num'], row=row).value).strip(),
                    'status': status,
                    'school': school,
                    'is_site_coordinator': is_site_coordinator,
                    'students': []
                    }
            pd1 = ws.cell(column=COLS['STAFF_PD1']['num'], row=row).value
            pd2 = ws.cell(column=COLS['STAFF_PD2']['num'], row=row).value

            #if ws.cell(column=18, row=row).value:
            #    pd1 = ws.cell(column=18, row=row).value
            #teacher['is_visit_1_trained'] = pd1
            #teacher['is_visit_2_trained'] = pd2
            teacher['is_visit_1_trained'] = None
            teacher['is_visit_2_trained'] = None
            if isinstance(pd1, datetime.date):
                teacher['is_visit_1_trained'] = pd1
            if isinstance(pd2, datetime.date):
                teacher['is_visit_2_trained'] = pd2

            staff.append(teacher)

        if ws.cell(column=COLS['STUDENT_ID']['num'], row=row).value:
            grade = str(ws.cell(column=COLS['STUDENT_GRADE']['num'], row=row).value).strip()

            if grade: # is not None and grade is not '':
                grade = grade.replace('th','').replace('rd', '').replace('st', '').replace('nd', '')
                if grade in ('PREK', 'Pre-K'):
                    grade = -1
                elif grade in ('K', 'KD'):
                    grade = 0
                else:
                    grade = int(grade)
            else:
                messages.append("Row %d: No grade. Skipping" % row)
                continue

            gender = ws.cell(column=COLS['STUDENT_GENDER']['num'], row=row).value
            if gender:
                gender = gender.strip()
            else:
                gender = ''

            action = ws.cell(column=COLS['ACTION']['num'], row=row).value
            if action:
                action = action.strip()

            student = {
                    'first_name': ws.cell(column=COLS['STUDENT_FN']['num'], row=row).value.strip().title(),
                    'last_name': ws.cell(column=COLS['STUDENT_LN']['num'], row=row).value.strip().title(),
                    'id': ws.cell(column=COLS['STUDENT_ID']['num'], row=row).value,
                    'action': action,
                    'grade': grade,
                    'gender': gender,
                    'racial_identity': str(ws.cell(column=COLS['STUDENT_ETHNICITY']['num'], row=row).value).strip(),
                    }
            teacher['students'].append(student)
        elif ws.cell(column=COLS['STUDENT_FN']['num'], row=row).value:
            messages.append("Row %d: No student ID. Skipping" % row)
            continue

      except Exception as m:
          #messages.append("Error row %d: %s" % (row, str(m)))
        raise Exception("Row %d: %s" % (row, str(m)))

    for teacher in staff:
        if not teacher['position']:
           teacher['position'] = 'Unknown'
        #print teacher,school
        if not teacher['status']:
            teacher['status'] = 'Unknown'

        try:
            t,created = models.Staff.objects.get_or_create(
                    email=teacher['email'],
                    defaults={
                        'first_name': teacher['first_name'],
                        'last_name': teacher['last_name'],
                        'school': teacher['school'],
                        'is_site_coordinator': teacher['is_site_coordinator'],
                        'position':teacher['position'],
                        'is_visit_1_trained':teacher['is_visit_1_trained'],
                        'is_visit_2_trained':teacher['is_visit_2_trained'],
                        'status': models.StaffStatus.objects.get_or_create(name=teacher['status'])[0],
                    }
                    )
        except Exception as m:
            raise Exception("Teacher email '%s': %s" % (teacher['email'], str(m)))

        if not created:
            t.first_name = teacher['first_name']
            t.last_name = teacher['last_name']
            if t.school != teacher['school']:
                t.secondary_schools.add(teacher['school'])
            t.is_site_coordinator = teacher['is_site_coordinator']
            t.position = teacher['position']
            t.is_visit_1_trained = teacher['is_visit_1_trained']
            t.is_visit_2_trained = teacher['is_visit_2_trained']
            t.status = models.StaffStatus.objects.get_or_create(name=teacher['status'])[0]
            t.save()
        else:
            messages.append("Created %s" % (t))

        for s in teacher['students']:
            if s['action'] == 'Reassign Student':
                try:
                    stu = models.Student.objects.get(school=teacher['school'],student_id=s['id'])
                    stu.classroom_teachers.clear()
                    stu.classroom_teachers.add(t)
                except Exception as e:
                    raise Exception("Getting Student with ID %s: %s" % (s['id'], str(e)))
            elif s['action'] == 'INACTIVE':
                try:
                    stu = models.Student.objects.get(school=teacher['school'],student_id=s['id'])
                    stu.is_active = False
                    stu.save()
                except Exception as e:
                    raise Exception("Getting Student with ID %s: %s" % (s['id'], str(e)))
            elif not s['action']:
                try:
                    stu,c = models.Student.objects.get_or_create(
                        student_id=s['id'],
                        school=teacher['school'],
                        defaults={
                            'first_name': s['first_name'],
                            'last_name': s['last_name'],
                            'grade': s['grade'],
                            'gender': s['gender'],
                            'racial_identity': s['racial_identity'],
                            },
                        )
                    if not c:
                        stu.first_name = s['first_name']
                        stu.last_name = s['last_name']
                        stu.grade = s['grade']
                        stu.gender = s['gender']
                        stu.racial_identity = s['racial_identity']
                        stu.save()
                    else:
                        messages.append("Created: %s" % (s))
                    stu.classroom_teachers.add(t)
                except Exception as e:
                    raise Exception("Getting or Creating Student with ID %s: %s" % (s['id'], str(e)))
            else:
                raise Exception("Unknown action: '%s'" % s['action'])
    #transaction.rollback()
    return (staff, messages)


if __name__ == '__main__':
    import sys
    from django import setup

    setup()

    #transaction.set_autocommit(False)
    import_school_workbook(sys.argv[1], district_name=sys.argv[2])
