#!/usr/bin/env python
import datetime
import os

from openpyxl import Workbook
from openpyxl.cell import get_column_letter
from openpyxl.comments import Comment
from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font

import xlsxwriter


import django
django.setup()
from django.db.models import Q

import tempfile

from visit import models
from qa import models as qamodels


#build a list of slugs for each visit 'type'.  this will be used in build_cols

def build_visit_cols():
    return {
        'id': { 'type': 'visit', 'title': 'Visit ID', 'attr': 'id'},
        'school': { 'type': 'visit', 'title': 'School', 'attr': 'student.school.name'},
        'program_model': { 'type': 'visit', 'title': 'Program Model', 'attr': 'student.school.get_program_model_display()'},
        'staff1': { 'type': 'visit', 'title': 'Staff 1', 'attr': 'staff1.name'},
        'staff1-position': { 'type': 'visit', 'title': 'Staff 1 Position', 'attr': 'staff1.position'},
        'staff2': { 'type': 'visit', 'title': 'Staff 2', 'attr': 'staff2.name'},
        'staff2-position': { 'type': 'visit', 'title': 'Staff 2 Position', 'attr': 'staff2.position'},
        'student-first-name': { 'type': 'visit', 'title': 'Student First Name', 'attr': 'student.first_name'},
        'student-last-name': { 'type': 'visit', 'title': 'Student Last Name', 'attr': 'student.last_name'},
        'submit-date': { 'type': 'visit', 'title': 'Submit Date', 'attr': 'date_submitted'},
        'grade': { 'type': 'visit', 'title': ' Grade', 'attr': 'student.grade'},
        'district-id': { 'type': 'visit', 'title': 'District ID', 'attr': 'student.student_id'},
        'gender': { 'type': 'visit', 'title': 'Gender', 'attr': 'student.gender'},
        'racial-identity': { 'type': 'visit', 'title': 'Racial Identity ', 'attr': 'student.racial_identity'},
    }

def build_cols(questionset):
    cols = {}
    for q in questionset.questions.all(): #.filter(is_custom=False):
        if q.is_custom: continue
        if q.type == 'choices' and q.choices.count() > 2:
            for c in q.choices.all():
                cols['%s__%d' % (q.slug, c.id)] = { 'type': q.type, 'title': "%s: %s" % (q.slug, c.title) }
        elif q.type == 'choices-detail' and q.choices.count() > 2:
            for c in q.choices.all():
                cols['%s__%d__bool' % (q.slug, c.id)] = { 'type': q.type, 'title': "%s: %s" % (q.slug, c.title) }
                cols['%s__%d__detail' % (q.slug, c.id)] = { 'type': q.type, 'title': "Improvement in %s" % (c.title) }


        else:
            cols[q.slug] = { 'type': 'question', 'title': q.name , 'comment': q.explanation }
    return cols

def build_adict():
    adict = {}
    for a in qamodels.Answer.objects.all().select_related('question','answerset__visit').prefetch_related('selections', 'selections__questionchoice'):
        if not hasattr(a.answerset, 'visit'): continue
        if a.answerset.visit.id not in adict:
            adict[a.answerset.visit.id] = {}
        if a.question.slug not in adict[a.answerset.visit.id]:
            adict[a.answerset.visit.id][a.question.slug] = a
    return adict

def build_sdict():
    sdict = {}
    v = 'answer__answerset__visit'
    slug = 'questionchoice__question__slug'
    qc = 'questionchoice__id'
    selections = qamodels.Selection.objects.all().values(qc, 'text', 'id', 'answer_id', 'answer__answerset', v, 'questionchoice__is_freeform', slug)
    for s in selections:
        if s[v] not in sdict:
            sdict[s[v]] = {}
        if s[slug] not in sdict[s[v]]:
            sdict[s[v]][s[slug]] = {}
        if s[qc] not in sdict[s[v]][s[slug]]:
            sdict[s[v]][s[slug]][s[qc]] = s
    return sdict

def get_value(cols, field, visit, S_DICT, A_DICT):
    if cols[field]['type'] == 'choices-detail':
        q_slug,c_id,typ = field.split('__')
        if typ == 'bool':
            try:
                if 'text' in S_DICT[visit.id][q_slug][int(c_id)]:
                    pass
                return 'Yes'
            except KeyError:
                # It was not selected as something that needs to be reported on
                return "No"

        elif typ == 'detail':
            try:
                return S_DICT[visit.id][q_slug][int(c_id)]['text']
            except KeyError:
                # It was not selected as something that needs to be reported on so don't report anything
                return ""

    if cols[field]['type'] == 'choices':
        q_slug,c_id = field.split('__')
        try:
            #selection = visit.answerset.answers.get(question__slug=q_slug).selections.get(questionchoice_id=int(c_id))
            #if selection.questionchoice.is_freeform:
            if S_DICT[visit.id][q_slug][int(c_id)]['questionchoice__is_freeform']:
                return S_DICT[visit.id][q_slug][int(c_id)]['text']
                #return selection.text
            else:
                return "Yes" #selection.get_value()
        except:
            return "No"
    if cols[field]['type'] == 'question':
        try:
            if field not in A_DICT[visit.id]:
                return ""
            answer = A_DICT[visit.id][field] #visit.answerset.answers.get(question__slug=field)
            #answer = visit.answerset.answers.get(question__slug=field)
            v = answer.get_str()
            return v
        except qamodels.Answer.DoesNotExist:
            return ""
    elif cols[field]['type'] == 'visit':
        try:
            return eval("visit.%s" % cols[field]['attr'])
        except:
            return ""
    else:
        return "TODO"

def get_or_create_worksheet(wb, v, cols, fields):
    if v.answerset.questionset.title not in wb.sheetnames:
        ws = wb.create_sheet(title=v.answerset.questionset.title)
        for i,f in enumerate(fields):
            c = ws.cell(row=1, column=i+1)
            c.value = cols[f]['title']
            if ws.column_dimensions[get_column_letter(i+1)].width < len(str(c.value)):
                ws.column_dimensions[get_column_letter(i+1)].width = len(str(c.value))
            if cols[f].get('comment', ''):
                c.comment = Comment(cols[f]['comment'], 'Portal')
            c.fill = PatternFill(fill_type='lightGray') #, fgColor='FFdddddd')
    else:
        ws = wb[v.answerset.questionset.title]
    return ws

def make_row_from_cols(ws, fields, cols, S_DICT, A_DICT, visit=None, row=0, headers=False, formats=None):
    for i, f in enumerate(fields):
        value = ""
        #c = ws.cell(row=row, column=i+1)
        if headers:
            if cols[f]['type'] == 'choices':
                value = cols[f]['title']
            else:
                value = cols[f]['title']
            #c.fill = PatternFill(fill_type='lightGray') #, fgColor='FFdddddd')
            #if cols[f].get('comment', None):
            #    c.comment = Comment(cols[f]['comment'], 'Portal')
        else:
            value = get_value(cols, f, visit, S_DICT, A_DICT)
        #if ws.column_dimensions[get_column_letter(i+1)].width < len(str(c.value)):
        #    ws.column_dimensions[get_column_letter(i+1)].width = len(str(c.value))
        if isinstance(value, datetime.datetime):
            ws.write(row, i, value, formats['datetime'])
        elif isinstance(value, datetime.date):
            ws.write(row, i, value, formats['date'])
        elif isinstance(value, datetime.time):
            ws.write(row, i, value, formats['time'])
        else:
            ws.write(row, i, value)

def make_row(ws, cols, row=1, headers=False):
    for i, f in enumerate(cols):
        c = ws.cell(row=row, column=i+1)
        c.value = f
        if ws.column_dimensions[get_column_letter(i+1)].width < len(str(c.value)):
            ws.column_dimensions[get_column_letter(i+1)].width = len(str(c.value))
        if headers:
            c.fill = PatternFill(fill_type='lightGray') #, fgColor='FFdddddd')
    
def student_visits_xls(schools, date_begin, date_end):
    wb = Workbook()
    wb.remove_sheet(wb.active)
    worksheets = {}
    ws = wb.create_sheet(title="Student Visists")
    make_row(ws, ["Student Name", "School", "First Staff", "Second Staff", "Visit Status", "Visit Date", "Visit #", "Visit ID",], headers=True)
    q = Q(staff1__school__in=set(schools)) | Q(staff1__secondary_schools__in=set(schools))
    row = 2
    #for v in models.Visit.objects.filter(q).filter(is_submitted=True).filter(date_submitted__range=(date_begin, date_end)).order_by('student__school', 'student').distinct():
    current_school = None
    for v in models.Visit.objects.filter(q).filter(is_submitted=True).order_by('student__school', 'student').distinct():
        if current_school is None:
            total_complete = 0
            total_noshow = 0
        elif current_school != v.student.school.name:
            make_row(ws, ["Total Completed Visits", total_complete], row)
            make_row(ws, ["Total No-Show Visits", total_noshow], row+1)
            row += 2
            total_complete = 0
            total_noshow = 0
        current_school = v.student.school.name
        if v.type == 'complete':
            total_complete += 1
        elif v.type == 'noshow':
            total_noshow += 1
        if v.type == 'contact_attempt':
            date = v.date_submitted
        else:
            date = v.answerset.answers.get(question__slug='visit-date').get_value()
        if date < date_begin or date > date_end: continue
        if 'First' in v.answerset.questionset.title:
            vn = 1
        elif 'Second' in v.answerset.questionset.title:
            vn = 2
        else:
            vn = "Unknown"
        if v.staff2:
            staff2 = v.staff2.name
        else:
            staff2 = None
        make_row(ws, [v.student.name, v.student.school.name, v.staff1.name, staff2, v.type, date.strftime("%m-%d-%Y"), vn, v.id ], row=row)
        row += 1
    make_row(ws, ["Total Completed Visits", total_complete], row)
    make_row(ws, ["Total No-Show Visits", total_noshow], row+1)
    tmpfile = tempfile.mkstemp()[1]
    wb.save(tmpfile)
    fobj = open(tmpfile)
    os.unlink(tmpfile)
    return fobj

def payroll(schools, date_begin, date_end):
    wb = Workbook()
    wb.remove_sheet(wb.active)
    worksheets = {}
    ws = wb.create_sheet(title="Payroll")
    make_row(ws, ["Staff Name", "Student Name", "School", "Visit Status", "Visit Date", "Visit #", "Visit ID",  "Total Complete", "Total Incomplete"], headers=True)

    q = Q(school__in=schools) | Q(secondary_schools__in=schools)
    staff = list(models.Staff.objects.filter(q).distinct())
    row=2
    for s in set(staff):
        total_complete = 0
        total_noshow = 0
        for v in s.all_visits().filter(is_submitted=True).exclude(type='contact_attempt'): #.filter(date_submitted__range=(date_begin, date_end)):
            visit_date = v.answerset.answers.get(question__slug='visit-date').get_value()
            if visit_date < date_begin or visit_date > date_end: continue
            if v.type == 'complete':
                total_complete += 1
            elif v.type == 'noshow':
                total_noshow += 1
            if 'First' in v.answerset.questionset.title:
                vn = 1
            elif 'Second' in v.answerset.questionset.title:
                vn = 2
            else:
                vn = "Unknown"
            make_row(ws, [s.name, v.student.name, v.student.school.name, v.type, visit_date.strftime("%m-%d-%Y"), vn, v.id ], row=row)
            row += 1
        else:
            if total_complete > 0 or total_noshow > 0:
                make_row(ws, [s.name,'','','','','', '', total_complete, total_noshow], row=row)
                row += 1
    tmpfile = tempfile.mkstemp()[1]
    wb.save(tmpfile)
    fobj = open(tmpfile)
    os.unlink(tmpfile)
    return fobj

def data_directory_xls():
    wb = Workbook()
    wb.remove_sheet(wb.active)
    ws = wb.create_sheet(title="Data Directory")
    make_row(ws, ["District", "School", "Staff Last Name", "Staff First Name", "Staff Email", "Student ID", "Student Last Name", "Student First Name", "Manually Entered", "Grade", "Gender", "Ethnicity"], headers=True)
    row=2
    for staff in models.Staff.objects.all().order_by('school'):
        if staff.students.all().count() == 0:
            make_row(ws, [staff.school.district.name, staff.school.name, staff.last_name, staff.first_name, staff.email, '', '', '', '', '', '', ''], row=row)
            row += 1
        else:
            for stu in staff.students.all():
                make_row(ws, [stu.school.district.name, stu.school.name, staff.last_name, staff.first_name, staff.email, stu.student_id, stu.last_name, stu.first_name, stu.manually_added, stu.grade, stu.gender, stu.racial_identity], row=row)
                row += 1

    tmpfile = tempfile.mkstemp()[1]
    wb.save(tmpfile)
    fobj = open(tmpfile)
    os.unlink(tmpfile)
    return fobj

field_cache = {}

def build_fields(visit):
    if visit.answerset.questionset.id not in field_cache:
        fields = [
        'id',
        'submit-date',
        'school',
        'program_model',
        'staff1',
        'staff1-position',
        'staff2',
        'staff2-position',
        ]
        for q in visit.answerset.questionset.questions.all(): #filter(is_custom=False):
            if q.is_custom: continue
            if q.type == 'choices' and q.choices.count() > 2:
                for c in q.choices.all():
                    fields.append('%s__%d' % (q.slug, c.id))
            elif q.type == 'choices-detail' and q.choices.count() > 2:
                for c in q.choices.all():
                    fields.append('%s__%d__bool' % (q.slug, c.id))
                    fields.append('%s__%d__detail' % (q.slug, c.id))
    
            else:
                fields.append(q.slug)
        field_cache[visit.answerset.questionset.id] = fields
    return field_cache[visit.answerset.questionset.id]

def admin_report_xls():
    S_DICT = build_sdict()
    A_DICT = build_adict()
    visits = models.Visit.objects.filter(is_submitted=True).select_related('answerset__questionset').prefetch_related('answerset__questionset')
    #wb = Workbook()
    #wb.remove_sheet(wb.active)
    tmpfile = tempfile.mkstemp()[1]
    wb = xlsxwriter.Workbook(tmpfile, {'constant_memory': True})
    formats = {}
    formats['date'] = wb.add_format({'num_format': 'mm/dd/yyyy'})
    formats['datetime'] = wb.add_format({'num_format': 'mm/dd/yyyy hh:mm AM/PM'})
    formats['time'] = wb.add_format({'num_format': 'hh:mm AM/PM'})

    wsd = {}

    for i,v in enumerate(visits):
        cols = build_cols(v.answerset.questionset)
        cols.update(build_visit_cols())
        fields = build_fields(v)
        title = v.answerset.questionset.title
        if title not in wsd:
        #if v.answerset.questionset.title not in wb.sheetnames:
            ws = wb.add_worksheet(title)
            wsd[title] = { 'row': 0, 'ws': ws }
            #ws = wb.create_sheet(title=v.answerset.questionset.title)
            make_row_from_cols(ws, fields, cols, S_DICT, A_DICT, headers=True, formats=formats)
            wsd[title]['row'] += 1
        else:
            ws = wsd[title]['ws']
        make_row_from_cols(ws, fields, cols, S_DICT, A_DICT, visit=v, row=wsd[title]['row'], formats=formats)
        wsd[title]['row'] += 1

    #wb.save(tmpfile)
    wb.close()
    fobj = open(tmpfile)
    os.unlink(tmpfile)
    return fobj
