# -*- encoding: utf8 -*-

"""
白条非去哪儿收入确认表
"""
from __future__ import unicode_literals
import os

import datetime
import numpy as np
import pandas as pd

from db_con.db_connect import sql_engine

pd.options.mode.chained_assignment = None
pd.set_option('display.float_format', lambda x: '%.5f' % x)
mysql = sql_engine('audit', 'audit', False).get_engine()

# todo: is_active = -2 的订单，按哪个时间来关联风险金比例
sql_loan = """
SELECT
  bo.order_no            产品编号,
  bo.ref_id,
  fc.fund_name           资金方,
  bo.loan_paid_at        放款时间,
  case bo.is_active when 1 then bo.loan_paid_at when -2 then bo.train_cancel_at end train_cancel_at,
  year(bo.loan_paid_at)  年,
  month(bo.loan_paid_at) 月,
  bo.funding_loan_amount 放款金额,
  bo.is_active
FROM baitiao_audit.baitiao_order bo
  JOIN baitiao_audit.fund_corp fc
    ON fc.id = bo.funding_corp_id AND bo.funding_corp_id = 5 AND bo.is_active in (1, -2) 
    AND bo.loan_paid_at >= '2017-08-01' AND bo.loan_paid_at < '2017-11-01'
"""

sql_ref = """SELECT
  bo.ref_id,
  brprrr.ref_amount                            实还总额,
  brprrr.principle - brprrr.mitigate_principle 实还本金,
  brprrr.interest - brprrr.mitigate_interest   实还服务费,
  rra1006.repaid_at                            还款时间,
  rra1006.repay_channel_flow_id                流水号,
  rc.approach_name                             还款账户
FROM new_transaction.baitiao_repay_plan_repay_record_ref brprrr
  JOIN test_mzh.repay_record_amount rra1006
    ON rra1006.record_id = brprrr.record_id AND rra1006.repay_channel = brprrr.repay_channel
  JOIN new_transaction.user_bt_repayment_plan ubrp ON ubrp.id = brprrr.plan_id
  JOIN baitiao_audit.baitiao_order bo ON bo.ref_id = ubrp.ref_id AND bo.funding_corp_id = 5
  JOIN new_transaction.repay_channel rc ON rc.id = brprrr.repay_channel"""

sql_class = """
SELECT order_no 产品编号, class 风险等级 FROM test_mzh.bt_all_class WHERE order_no IN {} 
"""

sql_ratio = """
SELECT class 风险等级, year 年, month 月, bad_debt_ratio 计提风险金比例 FROM test_mzh.bt_service_fee_ratio
"""

df_loan_gen = pd.read_sql(sql=sql_loan, con=mysql, chunksize=10000)
res = []
for tmp in df_loan_gen:
    res.append(tmp)
df_loan = pd.concat(res)
df_repay_gen = pd.read_sql(sql=sql_ref, con=mysql, chunksize=10000)
res = []
for tmp in df_repay_gen:
    res.append(tmp)
df_repay = pd.concat(res)
df_ratio = pd.read_sql(sql=sql_ratio, con=mysql)

step = 5000
order_nos = tuple(df_loan['产品编号'].astype(str).unique())
res = []
for i in xrange(0, len(order_nos), step):
    tmp = pd.read_sql(sql=sql_class.format(str(order_nos[i:i + step]).replace(',)', ')')), con=mysql)
    res.append(tmp)
df_class = pd.concat(res)
df_class.drop_duplicates(['产品编号'], inplace=True)
df_loan = pd.merge(df_loan, df_class, on='产品编号', how='left')

print('{}, {}'.format(len(order_nos), df_class['产品编号'].nunique()))
print(tuple(df_loan.loc[df_loan['风险等级'].isnull(), '产品编号'].astype(str)))

df_loan = pd.merge(df_loan, df_ratio, on=['风险等级', '年', '月'])
df_loan['计提风险金'] = np.round(df_loan['放款金额'] * df_loan['计提风险金比例'], 2)

print(df_loan.groupby([df_loan['资金方'], df_loan['放款时间'].dt.month])['放款金额'].agg('sum').reset_index())
print(df_repay.groupby(df_repay['还款时间'].dt.month)['实还服务费'].agg('sum').reset_index())

view_dates = [datetime.date(2017, 9, 1), datetime.date(2017, 10, 1), datetime.date(2017, 11, 1)]
file_path = 'E:/审计出表/白条/收入确认表/1108'
res_sum = []
for v_date in view_dates:
    df_loan_v = df_loan.loc[(df_loan['放款时间'] < v_date) & (df_loan['train_cancel_at'] < v_date)]
    df_repay_v = df_repay.loc[df_repay['还款时间'] < v_date]

    no_loan_ref_ids = df_repay_v.loc[~df_repay_v.ref_id.isin(df_loan_v.ref_id), 'ref_id']
    print('有还款，没有放款的len: {0}，ref_id: {1}'.format(len(no_loan_ref_ids), str(tuple(no_loan_ref_ids))))

    gp = df_repay_v.groupby('ref_id')['实还服务费'].agg('sum').reset_index()
    df_loan_v = pd.merge(df_loan_v, gp, on='ref_id', how='left')

    df_loan_v.fillna(0, inplace=True)
    # todo: 检查是否有失败或退款订单有实还

    df_loan_v['assets'] = 0
    df_loan_v['收入'] = 0
    df_loan_v['减值'] = np.round(df_loan_v['计提风险金'] - df_loan_v['实还服务费'], 2)

    df_loan_v.loc[df_loan_v['放款金额'] > 0, 'assets'] = np.maximum(
        df_loan_v.loc[df_loan_v['放款金额'] > 0, '减值'], 0)
    df_loan_v.loc[df_loan_v['放款金额'] > 0, '收入'] = np.minimum(
        df_loan_v.loc[df_loan_v['放款金额'] > 0, '减值'], 0)

    df_loan_v.loc[df_loan_v['放款金额'] < 0, 'assets'] = np.minimum(
        df_loan_v.loc[df_loan_v['放款金额'] < 0, '减值'], 0)
    df_loan_v.loc[df_loan_v['放款金额'] < 0, '收入'] = np.maximum(
        df_loan_v.loc[df_loan_v['放款金额'] < 0, '减值'], 0)

    df_loan_v.drop(['ref_id', '减值'], axis=1, inplace=True)
    df_loan_v['放款时间'] = df_loan_v['放款时间'].dt.date
    print(df_loan_v[['放款金额', '计提风险金', '实还服务费', 'assets', '收入']].sum())

    # tmp_sum = df_loan_v.groupby(u'风险等级')[
    #     [u'放款金额', u'计提风险金', u'实还服务费', u'assets', u'收入']].agg('sum').reset_index()
    tmp_sum = df_loan_v[[u'放款金额', u'计提风险金', u'实还服务费', u'assets', u'收入']].agg('sum').to_frame().T
    tmp_sum[u'截止时间'] = v_date
    res_sum.append(tmp_sum)
    df_loan_v.drop(['train_cancel_at'], axis=1, inplace=True)
    df_loan_v.to_csv(os.path.join(file_path, '白条收入确认表_{}_{:0>2}.csv'.format(v_date.year, v_date.month - 1)), index=None,
                     encoding='gbk')

df_sum = pd.concat(res_sum)
df_sum.to_excel(os.path.join(file_path, '白条非去哪儿汇总.xlsx'), index=None)
