from py_jftech import read, where, format_date, to_tuple, mapper_columns, write

__COLUMNS__ = {
    'rfn_fund_id': 'fund_id',
    'rfn_date': 'nav_date',
    'rfn_nav_cal': 'nav_cal',
}

__INSERT_COLUMNS__ = {
    **__COLUMNS__,
    'rfn_av': 'av',
    'rfn_div': 'div',
    'rfn_split': 'split',
    'rfn_accrue_split': 'accrue_split',
    'rfn_av_p': 'av_p',
    'rfn_div_p': 'div_p',
}


@write
def batch_insert(datas):
    datas = [mapper_columns(x, __INSERT_COLUMNS__, ignore_none=False) for x in datas]
    values = ','.join([f'''({','.join([(f"'{x[j]}'" if j in x and x[j] is not None else 'null') for j in __INSERT_COLUMNS__.keys()])})''' for x in datas])
    return f'''insert into robo_fund_navs({','.join(__INSERT_COLUMNS__.keys())}) values {values}'''


@read
def get_navs(fund_id=None, min_date=None, max_date=None):
    sqls = []
    if min_date:
        sqls.append(f"rfn_date >= '{format_date(min_date)}'")
    if max_date:
        sqls.append(f"rfn_date <= '{format_date(max_date)}'")
    return f'''
    select {','.join([f"`{x[0]}` as `{x[1]}`" for x in __COLUMNS__.items()])} from robo_fund_navs 
    {where(*sqls, rfn_fund_id=to_tuple(fund_id))} order by rfn_fund_id, rfn_date
    '''


@read
def get_min_dates(fund_ids=None):
    return f'''
    select rfn_fund_id as fund_id, min(rfn_date) as min_date from robo_fund_navs {where(rfn_fund_id=to_tuple(fund_ids))}
    group by rfn_fund_id
    '''


@read(one=True)
def get_last_one(fund_id, max_date=None):
    sql = f"rfn_date <= '{format_date(max_date)}'" if max_date else None
    return f'''
    select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_fund_navs 
    {where(sql, rfn_fund_id=fund_id)} order by rfn_date desc limit 1
    '''