import pandas as pd
from data.datasource.mysqldb import *
from data.samples import mysqlquery
'''
目的：提供业务数据，包括 order_no,loan_id,用户类型【直接使用risk_info】,渠道，申请时间，拒绝原因，首逾天数，最大逾期天数，放款期数
'''


def query_risk_info(order_nos):
    '''
    直接使用了线上的用户类型，而非分析库的计算得到的用户类型
    :param order_nos:
    :return:order_no,user_loan_type-- 策略使用的用户类型,审核拒绝原因，是否黑名单拒绝或其他拒绝
    '''
    res = []
    sql = '''
    select biz_no as order_no,
    ifnull(JSON_EXTRACT(audit_context_data, '$.user_loan_type_v3'),
    JSON_EXTRACT(audit_context_data, '$.user_loan_type_v4')) as user_loan_type,
    JSON_EXTRACT(audit_context_data, '$.filter_info_in_black') qg_black,
    JSON_EXTRACT(audit_context_data, '$."third_data_source#xy_neg_code"') as xy_black,
    audit_result
    from biz_audit_log_data 
    where biz_no in %s 
    '''
    for i in range(0, len(order_nos), 1000):
        print('----exe sql %d---- ' % i)
        res.append(pd.read_sql(sql % str(tuple(order_nos[i:i + 1000])), engine_risk))
    df= pd.concat(res)
    df.drop_duplicates(['order_no'],inplace=True)
    df['refuse']='其他'
    df.loc[df.audit_result==1,'refuse']='审核通过'
    df.loc[(df.audit_result!=1) & (df.qg_black> 0),'refuse']='命中QG黑名单'
    df.loc[(df.audit_result != 1) & (df.xy_black=='0'), 'refuse'] = '命中新颜黑名单'
    return df[['order_no','uesr_loan_type','refuse']]

def query_byloanid(loan_ids):
    '''
    数据源为分析库
    :param loan_ids:list 放款集
    :return:order_no,user_id,loan_id,用户类型,渠道，申请时间，拒绝原因，首逾天数，最大逾期天数，放款期数
    '''
    sql='''
    select t1.loan_id,t1.user_id,t2.order_no,t2.contract_term,if(t2.term_no==1,t2.passdue_day,null) as passdue_day,
    t1.applied_at,t1.applied_type,t1.applied_channel,if(t2.loan_id is not null,'已放款',if(t1.approval==1,'审核通过','审核未通过')) as reason,
    max(t2.passdue_day) as max_passdue_day
    from loan_application t1 
    left join loan_repay t2 on t1.loan_id=t2.loan_id and t2.repayment_status!=4
    where t1.loan_id in %s
    group by 1,2,3,4,5,6,7,8
    '''
    res=[]
    for i in range(0, len(loan_ids), 1000):
        print('----exe sql %d---- ' % i)
        res.append(pd.read_sql(sql % str(tuple(loan_ids[i:i + 1000])), engine_analysis_new))
    df = pd.concat(res)
    df.order_no = df.order_no.apply(lambda x:x.decode('utf8'))
    #== 剔除重复数据
    df.sort_values(['loan_id'],ascending=True,inplace=True)
    df.drop_duplicates(['loan_id'],keep='last',inplace=True)
    return df

def query_by_orderno(order_nos):
    '''
        数据源为分析库
        :param order_nos:list 放款集
        :return:order_no,user_id,loan_id,用户类型,渠道，申请时间，拒绝原因，首逾天数，最大逾期天数，放款期数 reason['已放款'，'审核通过','审核未通过','黑名单']
        '''
    sql = '''
        select t1.loan_id,t1.user_id,t2.order_no,t2.contract_term,if(t2.term_no==1,t2.passdue_day,null) as passdue_day,
        t1.applied_at,t1.applied_type,t1.applied_channel,if(t2.loan_id is not null,'已放款',if(t1.approval==1,'审核通过','审核未通过')) as reason,
        max(t2.passdue_day) as max_passdue_day
        from loan_application t1 
        left join loan_repay t2 on t1.loan_id=t2.loan_id and t2.repayment_status!=4
        where t1.order_no in %s
        group by 1,2,3,4,5,6,7,8
        '''
    res = []
    for i in range(0, len(order_nos), 1000):
        print('----exe sql %d---- ' % i)
        res.append(pd.read_sql(sql % str(tuple(order_nos[i:i + 1000])), engine_analysis_new))
    df = pd.concat(res)
    df.order_no = df.order_no.apply(lambda x: x.decode('utf8'))
    # == 剔除重复数据
    df.sort_values(['loan_id'], ascending=True, inplace=True)
    df.drop_duplicates(['loan_id'], keep='last', inplace=True)
    return df


def query_bydate(start_date,end_date,is_loan=True):
    '''
    获取指定日期的数据,如果 is_loan=True ,则为放款集 否则为申请集
    :param start_date:
    :param end_date:
    :return:dataframe
    '''
    if is_loan:
        sql='''
        select t1.loan_id,t1.user_id,t2.order_no,t2.contract_term,if(t2.term_no==1,t2.passdue_day,null) as passdue_day,
        t1.applied_at,t1.applied_type,t1.applied_channel,if(t2.loan_id is not null,'已放款',if(t1.approval==1,'审核通过','审核未通过')) as reason,
        max(t2.passdue_day) as max_passdue_day
        from loan_application t1 
        join loan_repay t2 on t1.loan_id=t2.loan_id and t2.repayment_status!=4
        where  t1.applied=1
        and t1.applied_at >= '%s' and t1.applied_at < '%s'
        group by 1,2,3,4,5,6,7,8
        ''' % (start_date,end_date)
    else:
        sql='''
        select t1.loan_id,t1.user_id,t2.order_no,t2.contract_term,if(t2.term_no==1,t2.passdue_day,null) as passdue_day,
        t1.applied_at,t1.applied_type,t1.applied_channel,if(t2.loan_id is not null,'已放款',if(t1.approval==1,'审核通过','审核未通过')) as reason,
        max(t2.passdue_day) as max_passdue_day
        from loan_application t1 
        left join loan_repay t2 on t1.loan_id=t2.loan_id and t2.repayment_status!=4
        where  t1.applied=1
        and t1.applied_at >= '%s' and t1.applied_at < '%s'
        group by 1,2,3,4,5,6,7,8
        ''' % (start_date,end_date)

    df = mysqlquery.query(sql,engine_analysis_new)
    df.sort_values(['loan_id'],ascending=True,inplace=True)
    df.drop_duplicates(['loan_id'],keep='last',inplace=True)
    tmp=query_risk_info(df.order_no.tolist())
    return pd.merge(df,tmp,on='order_no',how='left')



