import pandas as pd
from datasource import mysqldb, mongodb
import time
from dateutil.relativedelta import relativedelta
import datetime
import pickle


'''
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 = ['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',
                'dhb_overview_ntdun_last_call_time']



    # default time interval
    start_time_period = (datetime.date.today() - relativedelta(months=+7)).strftime("%Y-%m-%d 00:00:00")
    end_time_period = (datetime.date.today() - relativedelta(days=+17)).strftime("%Y-%m-%d 00:00:00")

    def __init__(self, features=None, sql=None, start_time_period=None, end_time_period=None,passdue_day=15):
        try:
            # if the para was not Series
            if features != None:
                self.features = features
        except Exception as e:
            print("'features' parameter type Error, it should be list or Series")
            if start_time_period != None:
                self.start_time_period = start_time_period
            if end_time_period != None:
                self.end_time_period = end_time_period
            if sql != None:
                self.sql = sql
            else:
                sql = '''
                select ''' + str(features).replace('[', '').replace(']', '').replace('\'','') + ''',applied_at,applied_from,applied_type,if(passdue_day>''' + str(passdue_day) + ''',1,0) as target
                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(passdue_day) + '''
                '''

    def dhb_features_prepocessing(self,dhb_loan):
        try:
            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
            # print('sql: ', self.sql.replace('@start_time_period', self.start_time_period).replace('@end_time_period',
            #                                                                                       self.end_time_period))

            # dhb_loan = pd.read_sql(
            #     self.sql.replace('@start_time_period', self.start_time_period).replace('@end_time_period',self.end_time_period),
            #     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])

            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(time.strftime('%Y.%m.%d %H:%M:%S', time.localtime(
            #     time.time())) + "提取了dhb " + self.start_time_period + "to" + self.end_time_period + "时段样本")
        # ignore exceptions such as "colmns doesn't exist"
        except Exception as e:
            print("data preprocessing ERR ",e)
            pass
        return dhb_loan



    def dhb_predict_with_pkl(self,test,pkl='./dhb_cuishou_jianzhi_v3.pkl',features=features):
        open_file = open(pkl, "rb")
        model = pickle.load(open_file)
        open_file.close()
        return model.predict(test[features])



    def dhb_dataSketch(self,df, given_dataset=None, start_time_period = start_time_period, end_time_period = end_time_period,
                   applied_type=None, applied_from=None):
        '''
        instructions : build a comparasion

        Params :
            df - test dataset which was given
            score - score column
            target - label
            start_time_period -
            end_time_period -
            applied_tpye -
            applied_from -
        Returns :
            auc comparasion
            liftchart plot
        '''
        limit = "{'wf_created_at': {'$gte': '@start_date', '$lt': '@end_date'}}"
        query = "{'order_id':1,'@key':1}"
        df_mongo = mongodb.querymongo(start_time_period, end_time_period,
                              limit.replace('@start_date', start_time_period).replace('@end_date', end_time_period),
                              "{'order_id':1,'model_exec_data_source#dhb':1}")
        df = pd.merge(df, df_mongo, how='left', left_on='order_no', right_on='order_id')
        df = df.dropna(axis=0)
        df.to_csv('./mvp/dhb_target.csv')
        return df


    def dhb_comparasion(df, score_BM='model_exec_data_source#dhb', score_predict='predict', target='target',applied_type=None, applied_from=None):
        '''
        instructions : obtain online dhb score from mongodb
        '''
        # spliting data with appliedType & applied_channel
        df = df[df.applied_type == applied_type]
        df = df[df.applied_from == applied_from]
        # cut up bins
        ## bins benchmark
        df['bins_BM'] = df.qcut(df[score_BM], q=10, percision=6, dupulicates='drop')
        ## bins of predictions
        df['bins_predict'] = df.qcut(df[score_predict], q=10, percision=6, dupulicates='drop')
        return 1



    def vlm(self,feature):
        return 1

    def psi(self,feature):
        return 1

    def liftchart(self,feature):
        return 1
















