import pandas as pd
from data.datasource import mysqldb,mongodb
import datetime
import dateutil
'''
model instructions : established a dhb obj which cotains attrubutes of dhb model

Params Constructor:
    _init__(self,features=None,sql=None,start_time_period=None,end_time_period=None)

API :
    .features - default features
    .start_time_period
    .end_time_period
    .dhb_features_extract() extract dhb features
    .dhb_comparasion(start_time_period = self.start_time_period, end_time_period = self.end_time_period, applied_type = None,applied_from = None)

'''


class dhb():
    # features as Series format
    features = ['dhb_last_30_and_60_days_dun_call_avg_duration',
    'dhb_last_30_and_60_days_dun_call_duration_above60',
    'dhb_last_30_and_60_days_dun_call_duration_below15',
    'dhb_last_30_and_60_days_dun_call_duration_between15_and_30',
    'dhb_last_30_and_60_days_dun_call_in_duration',
    'dhb_last_30_and_60_days_dun_call_in_times',
    'dhb_last_30_and_60_days_dun_call_out_duration',
    'dhb_last_30_and_60_days_dun_call_out_times',
    'dhb_last_30_and_60_days_dun_call_tel_total_nums',
    'dhb_last_30_and_60_days_dun_call_total_duration',
    'dhb_last_30_and_60_days_dun_call_total_times',
    'dhb_last_30_and_60_days_ntdun_call_avg_duration',
    'dhb_last_30_and_60_days_ntdun_call_duration_above60',
    'dhb_last_30_and_60_days_ntdun_call_duration_below15',
    'dhb_last_30_and_60_days_ntdun_call_duration_between15_and_30',
    'dhb_last_30_and_60_days_ntdun_call_in_duration',
    'dhb_last_30_and_60_days_ntdun_call_in_times',
    'dhb_last_30_and_60_days_ntdun_call_out_duration',
    'dhb_last_30_and_60_days_ntdun_call_out_times',
    'dhb_last_30_and_60_days_ntdun_call_tel_total_nums',
    'dhb_last_30_and_60_days_ntdun_call_total_duration',
    'dhb_last_30_and_60_days_ntdun_call_total_times',
    'dhb_last_30_days_dun_call_avg_duration',
    'dhb_last_30_days_dun_call_duration_above60',
    'dhb_last_30_days_dun_call_duration_below15',
    'dhb_last_30_days_dun_call_duration_between15_and_30',
    'dhb_last_30_days_dun_call_in_duration',
    'dhb_last_30_days_dun_call_in_times',
    'dhb_last_30_days_dun_call_out_duration',
    'dhb_last_30_days_dun_call_out_times',
    'dhb_last_30_days_dun_call_tel_total_nums',
    'dhb_last_30_days_dun_call_total_duration',
    'dhb_last_30_days_dun_call_total_times',
    'dhb_last_30_days_ntdun_call_avg_duration',
    'dhb_last_30_days_ntdun_call_duration_above60',
    'dhb_last_30_days_ntdun_call_duration_below15',
    'dhb_last_30_days_ntdun_call_duration_between15_and_30',
    'dhb_last_30_days_ntdun_call_in_duration',
    'dhb_last_30_days_ntdun_call_in_times',
    'dhb_last_30_days_ntdun_call_out_duration',
    'dhb_last_30_days_ntdun_call_out_times',
    'dhb_last_30_days_ntdun_call_tel_total_nums',
    'dhb_last_30_days_ntdun_call_total_duration',
    'dhb_last_30_days_ntdun_call_total_times',
    'dhb_last_60_and_90_days_dun_call_avg_duration',
    'dhb_last_60_and_90_days_dun_call_duration_above60',
    'dhb_last_60_and_90_days_dun_call_duration_below15',
    'dhb_last_60_and_90_days_dun_call_duration_between15_and_30',
    'dhb_last_60_and_90_days_dun_call_in_duration',
    'dhb_last_60_and_90_days_dun_call_in_times',
    'dhb_last_60_and_90_days_dun_call_out_duration',
    'dhb_last_60_and_90_days_dun_call_out_times',
    'dhb_last_60_and_90_days_dun_call_tel_total_nums',
    'dhb_last_60_and_90_days_dun_call_total_duration',
    'dhb_last_60_and_90_days_dun_call_total_times',
    'dhb_last_60_and_90_days_ntdun_call_avg_duration',
    'dhb_last_60_and_90_days_ntdun_call_duration_above60',
    'dhb_last_60_and_90_days_ntdun_call_duration_below15',
    'dhb_last_60_and_90_days_ntdun_call_duration_between15_and_30',
    'dhb_last_60_and_90_days_ntdun_call_in_duration',
    'dhb_last_60_and_90_days_ntdun_call_in_times',
    'dhb_last_60_and_90_days_ntdun_call_out_duration',
    'dhb_last_60_and_90_days_ntdun_call_out_times',
    'dhb_last_60_and_90_days_ntdun_call_tel_total_nums',
    'dhb_last_60_and_90_days_ntdun_call_total_duration',
    'dhb_last_60_and_90_days_ntdun_call_total_times',
    'dhb_last_three_weeks_dun_call_avg_duration',
    'dhb_last_three_weeks_dun_call_duration_above60',
    'dhb_last_three_weeks_dun_call_duration_below15',
    'dhb_last_three_weeks_dun_call_duration_between15_and_30',
    'dhb_last_three_weeks_dun_call_in_duration',
    'dhb_last_three_weeks_dun_call_in_times',
    'dhb_last_three_weeks_dun_call_out_duration',
    'dhb_last_three_weeks_dun_call_out_times',
    'dhb_last_three_weeks_dun_call_tel_total_nums',
    'dhb_last_three_weeks_dun_call_total_duration',
    'dhb_last_three_weeks_dun_call_total_times',
    'dhb_last_three_weeks_ntdun_call_avg_duration',
    'dhb_last_three_weeks_ntdun_call_duration_above60',
    'dhb_last_three_weeks_ntdun_call_duration_below15',
    'dhb_last_three_weeks_ntdun_call_duration_between15_and_30',
    'dhb_last_three_weeks_ntdun_call_in_duration',
    'dhb_last_three_weeks_ntdun_call_in_times',
    'dhb_last_three_weeks_ntdun_call_out_duration',
    'dhb_last_three_weeks_ntdun_call_out_times',
    'dhb_last_three_weeks_ntdun_call_tel_total_nums',
    'dhb_last_three_weeks_ntdun_call_total_duration',
    'dhb_last_three_weeks_ntdun_call_total_times',
    'dhb_last_two_weeks_dun_call_avg_duration',
    'dhb_last_two_weeks_dun_call_duration_above60',
    'dhb_last_two_weeks_dun_call_duration_below15',
    'dhb_last_two_weeks_dun_call_duration_between15_and_30',
    'dhb_last_two_weeks_dun_call_in_duration',
    'dhb_last_two_weeks_dun_call_in_times',
    'dhb_last_two_weeks_dun_call_out_duration',
    'dhb_last_two_weeks_dun_call_out_times',
    'dhb_last_two_weeks_dun_call_tel_total_nums',
    'dhb_last_two_weeks_dun_call_total_duration',
    'dhb_last_two_weeks_dun_call_total_times',
    'dhb_last_two_weeks_ntdun_call_avg_duration',
    'dhb_last_two_weeks_ntdun_call_duration_above60',
    'dhb_last_two_weeks_ntdun_call_duration_below15',
    'dhb_last_two_weeks_ntdun_call_duration_between15_and_30',
    'dhb_last_two_weeks_ntdun_call_in_duration',
    'dhb_last_two_weeks_ntdun_call_in_times',
    'dhb_last_two_weeks_ntdun_call_out_duration',
    'dhb_last_two_weeks_ntdun_call_out_times',
    'dhb_last_two_weeks_ntdun_call_tel_total_nums',
    'dhb_last_two_weeks_ntdun_call_total_duration',
    'dhb_last_two_weeks_ntdun_call_total_times',
    'dhb_last_week_dun_call_avg_duration',
    'dhb_last_week_dun_call_duration_above60',
    'dhb_last_week_dun_call_duration_below15',
    'dhb_last_week_dun_call_duration_between15_and_30',
    'dhb_last_week_dun_call_in_duration',
    'dhb_last_week_dun_call_in_times',
    'dhb_last_week_dun_call_out_duration',
    'dhb_last_week_dun_call_out_times',
    'dhb_last_week_dun_call_tel_total_nums',
    'dhb_last_week_dun_call_total_duration',
    'dhb_last_week_dun_call_total_times',
    'dhb_last_week_ntdun_call_avg_duration',
    'dhb_last_week_ntdun_call_duration_above60',
    'dhb_last_week_ntdun_call_duration_below15',
    'dhb_last_week_ntdun_call_duration_between15_and_30',
    'dhb_last_week_ntdun_call_in_duration',
    'dhb_last_week_ntdun_call_in_times',
    'dhb_last_week_ntdun_call_out_duration',
    'dhb_last_week_ntdun_call_out_times',
    'dhb_last_week_ntdun_call_tel_total_nums',
    'dhb_last_week_ntdun_call_total_duration',
    'dhb_last_week_ntdun_call_total_times',
    'dhb_overview_dun_call_avg_duration',
    'dhb_overview_dun_call_duration_above60',
    'dhb_overview_dun_call_duration_below15',
    'dhb_overview_dun_call_duration_between15_and_30',
    'dhb_overview_dun_call_in_duration',
    'dhb_overview_dun_call_in_times',
    'dhb_overview_dun_call_out_duration',
    'dhb_overview_dun_call_out_times',
    'dhb_overview_dun_call_tel_total_nums',
    'dhb_overview_dun_call_total_duration',
    'dhb_overview_dun_call_total_times',
    'dhb_overview_dun_first_call_time',
    'dhb_overview_dun_last_call_time',
    'dhb_overview_ntdun_call_avg_duration',
    'dhb_overview_ntdun_call_duration_above60',
    'dhb_overview_ntdun_call_duration_below15',
    'dhb_overview_ntdun_call_duration_between15_and_30',
    'dhb_overview_ntdun_call_in_duration',
    'dhb_overview_ntdun_call_in_times',
    'dhb_overview_ntdun_call_out_duration',
    'dhb_overview_ntdun_call_out_times',
    'dhb_overview_ntdun_call_tel_total_nums',
    'dhb_overview_ntdun_call_total_duration',
    'dhb_overview_ntdun_call_total_times',
    'dhb_overview_ntdun_first_call_time']

    def __init__(self,overdue_days=15,features=None,sql=None,start_time_period=None,end_time_period=None):
        try:
            if features != None:
                self.features = features
            if sql != None:
                self.sql = sql
            else:
                sql  = "select "+str(features).strip('[').strip(']')+''',if(passdue_day>'''+str(overdue_days)+''',1,0) as target, applied_at, applied_from, applied_type
                from risk_analysis
                where applied_at >= '@start_time_period' and applied_at < '@end_time_period'
                and transacted = 1
                and dhb_flag =1
                and datediff(now(),deadline) > '''+str(overdue_days)+'''
                '''

            if start_time_period != None:
                self.start_time_period = start_time_period
            else:
                self.start_time_period =(datetime.date.today() - dateutil.relativedelta(months=+7)).strftime("%Y-%m-%d 00:00:00")
            if end_time_period != None:
                self.end_time_period = end_time_period
            else:
                self.end_time_period = (datetime.date.today() - dateutil.relativedelta(days=+16)).strftime("%Y-%m-%d 00:00:00")
        except Exception as e:
            print('Parameters Error:\n',e)


    def dhb_features_extract(self):
        '''
        instrucions : extract dhb features from risk_analysis
        :param self:
        :return: dhb features
        '''

        value_map = {
            "近3天":1,
            "近4-5天":2,
            "近6-7天":3,
            "近8-15天":4,
            "近16-30天":5,
            "近31-60天":6,
            "近61-90天":7,
            "近91-120天":8,
            "近121-150天":9,
            "近151-180天":10,
            "180天前":11,
            "无":0
        }
        #print(self.sql.replace('@start_time_period',self.start_time_period).replace('@end_time_period',self.end_time_period))
        # use risk_analysis to extract data
        sql=self.sql.replace('@start_time_period',self.start_time_period).replace('@end_time_period',self.end_time_period)
        #
        dhb_loan = pd.read_sql(sql,mysqldb.engine_risk_analysis)
    #        dhb_loan[["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"]] = dhb_loan[["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"]].applymap(lambda x : value_map[x])

        # manipul category datatype which includes sequences
        dhb_loan[["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"]] = pd.get_dummies(dhb_loan[["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"]],columns=["dhb_overview_dun_first_call_time", "dhb_overview_dun_last_call_time","dhb_overview_ntdun_first_call_time", "dhb_overview_ntdun_last_call_time"])
        # limit the upper boundary
        dhb_loan.loc[dhb_loan.dhb_last_60_and_90_days_ntdun_call_avg_duration >= 42,"dhb_last_60_and_90_days_ntdun_call_avg_duration"] = 42
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_duration_above60 >= 25,"dhb_overview_ntdun_call_duration_above60"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_30_and_60_days_ntdun_call_total_duration>= 800,"dhb_last_30_and_60_days_ntdun_call_total_duration"] = 800
        dhb_loan.loc[dhb_loan.dhb_last_30_and_60_days_dun_call_in_duration >= 1600,"dhb_last_30_and_60_days_dun_call_in_duration"] = 1600
        dhb_loan.loc[dhb_loan.dhb_last_30_days_ntdun_call_total_duration>= 2500,"dhb_last_30_days_ntdun_call_total_duration"] = 2500
        dhb_loan.loc[dhb_loan.dhb_last_30_days_ntdun_call_tel_total_nums>= 25,"dhb_last_30_days_ntdun_call_tel_total_nums"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_30_days_dun_call_in_duration >= 1000,"dhb_last_30_days_dun_call_in_duration"] = 1000
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_total_duration >= 3000,"dhb_overview_ntdun_call_total_duration"] = 3000
        dhb_loan.loc[dhb_loan.dhb_overview_ntdun_call_in_times>= 25,"dhb_overview_ntdun_call_in_times"] = 25
        dhb_loan.loc[dhb_loan.dhb_last_60_and_90_days_ntdun_call_in_duration>= 1000,"dhb_last_60_and_90_days_ntdun_call_in_duration"] = 1000
        dhb_loan.loc[dhb_loan.dhb_overview_dun_call_tel_total_nums>= 22,"dhb_overview_dun_call_tel_total_nums"] = 22
        dhb_loan.loc[dhb_loan.dhb_last_30_days_dun_call_total_duration>= 1100,"dhb_last_30_days_dun_call_total_duration"] = 1100
        dhb_loan.loc[dhb_loan.dhb_last_two_weeks_ntdun_call_in_duration>= 300,"dhb_last_two_weeks_ntdun_call_in_duration"] = 300
        dhb_loan.to_csv("./dhb_loan_sample——"+str(datetime.date.today())+".csv")
        print( datetime.time.strftime('%Y.%m.%d %H:%M:%S',datetime.time.localtime(datetime.time.time())) +"提取了dhb {}+ ".format(str(self.overdue_days)) + self.start_time_period + "to" + self.end_time_period + "时段样本")
        return dhb_loan

    def dhb_comparasion(self,limit="{'wf_created_at': {'$gte': '@start_date', '$lt': '@end_date'}}",df=None,applied_type = None,applied_from = None):
        df_mongo = mongodb.pymongodb(self.start_time_period, self.end_time_period, limit, "{'order_id':1,'model_exec_data_source#dhb':1}")


        

        
