from passlib.hash import pbkdf2_sha256 as sha256
from datetime import datetime
from io import BytesIO
from PIL import Image
from dbsql import MySQL
# from mtcnn import MTCNN
from matplotlib import pyplot as plt
import os, json, base64, requests
from CM26EWH_model import insert_face,delete_person

env = os.environ
host = env['MYSQL_HOST']      if 'MYSQL_HOST' in env else "192.168.6.194"
port = int(env['MYSQL_PORT']) if 'MYSQL_PORT' in env else 23306
usr  = env['MYSQL_USR']       if 'MYSQL_USR'  in env else "root"
pwd  = env['MYSQL_PWD']       if 'MYSQL_PWD'  in env else "123456"
db   = env['MYSQL_DB']        if 'MYSQL_DB'   in env else "ecom"
mysql = MySQL(host, port, usr, pwd, db)
# detector = MTCNN()

# ===== 基礎資料庫模型 =====
class BaseModel(object):
    def __init__(self):
        self.data = { 'created': self.GetNow(), 'updated': self.GetNow(), }
        self.change = {}
        self.isExist = False
    def IsEnabled(self):
        return self.GetDeleted() is None
    def SetCreated(self, created=None):
        self.data['created'] = self.GetNow() if created is None else created
    def SetUpdated(self, updated=None):
        self.data['updated'] = self.GetNow() if updated is None else updated
    def SetDeleted(self, deleted=None):
        self.data['deleted'] = deleted
    def SetValue(self, key, value, valueType, canNull):
        if value is not None or canNull:
            value1 = value if isinstance(value, valueType) or canNull else valueType(value)
            if key in self.data and self.data[key] is not None:
                value2 = self.data[key] if isinstance(self.data[key], valueType) else valueType(self.data[key])
                if value1 != value2:
                    self.change[key] = self.data[key]
            self.data[key] = value1
            self.SetUpdated()
            return True
        return False
    def GetCreated(self):
        return self.StringToDatetime(self.data['created']) if 'created' in self.data and self.data['created'] is not None else None
    def GetUpdated(self):
        return self.StringToDatetime(self.data['updated']) if 'updated' in self.data and self.data['updated'] is not None else None
    def GetDeleted(self):
        return self.StringToDatetime(self.data['deleted']) if 'deleted' in self.data and self.data['deleted'] is not None else None
    def GetHash(self, password):
        return "" if password is None or len(password) == 0 else sha256.hash(password)
    def GetNow(self):
        return self.DatetimeToString(datetime.now())
    def DatetimeToString(self, _datetime):
        if isinstance(_datetime, datetime):
            return "{0}{1}".format(_datetime.year - 1911, _datetime.strftime('%m%d%H%M%S'))
        else:
            return None
    def StringToDatetime(self, _string):
        if isinstance(_string, str):
            y = int(_string[0:3]) + 1911
            _datetime = datetime.strptime(_string[3:13], "%m%d%H%M%S")
            _datetime = _datetime.replace(y)
            return _datetime
        else:
            return None
        
# ========== 請假資料庫模型 ==========
class LeaveOffWorkModel(BaseModel):
    def __init__(self, ID=None):
        super().__init__()
        self.table = 'leave_off_work'
        if(ID != None):
            self.data['no'] = ID if isinstance(ID, str) else str(ID) 
        self.data['depart_leave_unit_department'] = None
        self.data['person_id'] = None
        self.data['category'] = None
        self.data['reason'] = None
        self.data['start'] = None
        self.data['end'] = None
        self.data['leave_day'] = None
        self.data['leave_time'] = None
        self.data['substitute'] = None
        self.data['upload_file'] = None
    # ===== no ========================================
    def GetNo(self):
        return self.data['no'] if 'no' in self.data else None
    # ===== depart_leave_unit_department ========================================
    def SetDepartLeaveUnitDepartment(self, depart_leave_unit_department):
        return self.SetValue('depart_leave_unit_department', depart_leave_unit_department, str, False)
    def GetDepartLeaveUnitDepartment(self):
        return self.data['depart_leave_unit_department'] if 'depart_leave_unit_department' in self.data else None
    def GetOldDepartLeaveUnitDepartment(self):
        return self.change['depart_leave_unit_department']
    def HasChangeDepartLeaveUnitDepartment(self):
        return 'depart_leave_unit_department' in self.change
    # ===== person_id ========================================
    def SetPersonId(self, person_id):
        return self.SetValue('person_id', person_id, str, False)
    def GetPersonId(self):
        return self.data['person_id'] if 'person_id' in self.data else None
    def GetOldPersonId(self):
        return self.change['person_id']
    def HasChangePersonId(self):
        return 'person_id' in self.change
    # ===== category ========================================
    def SetCategory(self, category):
        return self.SetValue('category', category, str, False)
    def GetCategory(self):
        return self.data['category'] if 'category' in self.data else None
    def GetOldCategory(self):
        return self.change['category']
    def HasChangeCategory(self):
        return 'category' in self.change
    # ===== reason ========================================
    def SetReason(self, reason):
        return self.SetValue('reason', reason, str, False)
    def GetReason(self):
        return self.data['reason'] if 'reason' in self.data else None
    def GetOldReason(self):
        return self.change['reason']
    def HasChangeReason(self):
        return 'reason' in self.change
    # ===== start ========================================
    def SetStart(self, start):
        return self.SetValue('start', start, str, False)
    def GetStart(self):
        return self.data['start'] if 'start' in self.data else None
    def GetOldStart(self):
        return self.change['start']
    def HasChangeStart(self):
        return 'start' in self.change
    # ===== end ========================================
    def SetEnd(self, end):
        return self.SetValue('end', end, str, False)
    def GetEnd(self):
        return self.data['end'] if 'end' in self.data else None
    def GetOldEnd(self):
        return self.change['end']
    def HasChangeEnd(self):
        return 'end' in self.change
    # ===== leave_day ========================================
    def SetLeaveDay(self, leave_day):
        return self.SetValue('leave_day', leave_day, int, False)
    def GetLeaveDay(self):
        return self.data['leave_day'] if 'leave_day' in self.data else None
    def GetOldLeaveDay(self):
        return self.change['leave_day']
    def HasChangeLeaveDay(self):
        return 'leave_day' in self.change
    # ===== leave_time ========================================
    def SetLeaveTime(self, leave_time):
        return self.SetValue('leave_time', leave_time, float, False)
    def GetLeaveTime(self):
        return self.data['leave_time'] if 'leave_time' in self.data else None
    def GetOldLeaveTime(self):
        return self.change['leave_time']
    def HasChangeLeaveTime(self):
        return 'leave_time' in self.change
    # ===== annual_leave_discount ========================================
    def SetSubstitute(self, substitute):
        return self.SetValue('substitute', substitute, str, False)
    def GetSubstitute(self):
        return self.data['substitute'] if 'substitute' in self.data else None
    def GetOldSubstitute(self):
        return self.change['substitute']
    def HasChangeSubstitute(self):
        return 'substitute' in self.change
    # ===== upload_file ========================================
    def SetUploadFile(self, upload_file):
        if isinstance(upload_file, str) and os.path.isfile(upload_file):
            buffer = BytesIO()
            image = plt.imread(upload_file)
            image = Image.fromarray(image)
            image.save(buffer, format='JPEG')
            # convert image to base64 string
            upload_file = buffer.getvalue()
            upload_file = base64.b64encode(upload_file)
            upload_file = upload_file.decode('utf-8')
        return self.SetValue('upload_file', upload_file, str, False)
    def GetUploadFile(self):
        return self.data['upload_file'] if 'upload_file' in self.data else None
    def GetOldUploadFile(self):
        return self.change['upload_file']
    def HasChangeUploadFile(self):
        return 'upload_file' in self.change

    # ===== operate database ========================================
    def InsertToDB(self, log):
        keys = self.data.keys()
        values = [ self.data[x] for x in keys ]
        mysql.SetLog(log)
        script = mysql.Insert(self.table, keys, values)
        return script.Fetch()
    def UpdateToDB(self, log):
        script = None
        for k in self.data.keys():
            mysql.SetLog(log)
            script = mysql.Update(self.table, k, self.data[k]) if script is None else script.Add(k, self.data[k])
        script = script.Where('no', '=', self.data['no'])
        return script.FetchOne()
    def DeleteFromDB(self, log):
        script = mysql.Delete(self.table).Where('no', '=', self.data['no'])
        mysql.SetLog(log)
        return script.FetchOne()
    def ToDict(self):
        return {
            'no': self.GetNo(),
            'depart_leave_unit_department': self.GetDepartLeaveUnitDepartment(),
            'person_id': self.GetPersonId(),
            'category': self.GetCategory(),
            'reason': self.GetReason(),
            'start': self.GetStart(),
            'end': self.GetEnd(),
            'leave_day': self.GetLeaveDay(),
            'leave_time': self.GetLeaveTime(),
            'substitute': self.GetSubstitute(),
            'upload_file': self.GetUploadFile(),
            'created': self.DatetimeToString(self.GetCreated()),
        }
    # ===== other ========================================
    @staticmethod
    def FromDB(ID, log):            
        model = LeaveOffWorkModel(ID)
        mysql.SetLog(log)
        script = mysql.Select('leave_off_work').Where('no', '=', ID).FetchOne()
        if isinstance(script, tuple):
            model.SetDepartLeaveUnitDepartment(script[1])
            model.SetPersonId(script[2])
            model.SetCategory(script[3])
            model.SetReason(script[4])
            model.SetStart(script[5])
            model.SetEnd(script[6])
            model.SetLeaveDay(script[7])
            model.SetLeaveTime(script[8])
            model.SetSubstitute(script[9])
            model.SetUploadFile(script[10])
            model.SetCreated(script[11])
            model.SetUpdated(script[12])
            model.SetDeleted(script[13])
            model.isExist = True
        return model
    @staticmethod
    def FromDBByStartAndEnd(Person, Start, End, log):            
        model = LeaveOffWorkModel()
        mysql.SetLog(log)
        script = mysql.Select('leave_off_work').Where('person_id', '=', Person).And('start', '=', Start).And('end', '=', End).FetchOne()
        if isinstance(script, tuple):
            model.SetDepartLeaveUnitDepartment(script[1])
            model.SetPersonId(script[2])
            model.SetCategory(script[3])
            model.SetReason(script[4])
            model.SetStart(script[5])
            model.SetEnd(script[6])
            model.SetLeaveDay(script[7])
            model.SetLeaveTime(script[8])
            model.SetSubstitute(script[9])
            model.SetUploadFile(script[10])
            model.SetCreated(script[11])
            model.SetUpdated(script[12])
            model.SetDeleted(script[13])
            model.isExist = True
        return model    
    @staticmethod
    def AllFromDB(log):
        models = []
        mysql.SetLog(log)
        script = mysql.Select('leave_off_work').FetchAll()
        for row in script:
            model = LeaveOffWorkModel(row[0])
            model.SetDepartLeaveUnitDepartment(row[1])
            model.SetPersonId(row[2])
            model.SetCategory(row[3])
            model.SetReason(row[4])
            model.SetStart(row[5])
            model.SetEnd(row[6])
            model.SetLeaveDay(row[7])
            model.SetLeaveTime(row[8])
            model.SetSubstitute(row[9])
            model.SetUploadFile(row[10])
            model.SetCreated(row[11])
            model.SetUpdated(row[12])
            model.SetDeleted(row[13])
            model.isExist = True
            models.append(model)
        return models
    