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

from api import PortfoliosRisk

__COLUMNS__ = {
    'rhp_id': 'id',
    'rhp_date': 'date',
    'rhp_risk': 'risk',
    'rhp_div': 'dividend',
    'rhp_div_acc': 'div_acc',
    'rhp_rrs_id': 'signal_id',
    'rhp_rebalance': 'rebalance',
    'rhp_portfolios': 'portfolios',
    'rhp_nav': 'nav',
    'rhp_fund_av': 'fund_av',
    'rhp_fund_div': 'fund_div',
    'rhp_div_forecast': 'div_forecast',
    'rhp_asset_nav': 'asset_nav',
    'rhp_port_div': 'port_div',
    'v_nav_div_acc': 'acc_av',
}


@read
def get_list(risk: PortfoliosRisk = None, min_date=None, max_date=None, rebalance: bool = None):
    sqls = []
    if min_date:
        sqls.append(f"rhp_date >= '{format_date(min_date)}'")
    if max_date:
        sqls.append(f"rhp_date <= '{format_date(max_date)}'")
    return f'''
    select {','.join([f'{x[0]} as {x[1]}' for x in __COLUMNS__.items()])} from robo_hold_portfolios
    {where(*sqls, rhp_risk=risk, rhp_rebalance=rebalance)} order by rhp_risk, rhp_date
    '''


@read(one=True)
def get_one(day, risk: PortfoliosRisk):
    return f'''select {','.join([f'{x[0]} as {x[1]}' for x in __COLUMNS__.items()])} from robo_hold_portfolios {where(rhp_date=day, rhp_risk=risk)}'''


@read(one=True)
def get_last_one(risk: PortfoliosRisk = None, max_date=None, rebalance: bool = None, signal_id=None):
    sql = f"rhp_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_hold_portfolios
    {where(sql, rhp_risk=risk, rhp_rrs_id=signal_id, rhp_rebalance=rebalance)}
    order by rhp_date desc limit 1
    '''


def get_count(risk: PortfoliosRisk = None):
    @read(one=True)
    def exec():
        return f'''select count(*) as `count` from robo_hold_portfolios {where(rhp_risk=risk)}'''

    result = exec()
    return result['count']


@write
def insert(datas):
    datas = mapper_columns(datas=datas, columns=__COLUMNS__)
    return f'''
    insert into robo_hold_portfolios({','.join([x for x in datas.keys()])})
    values ({','.join([f"'{x[1]}'" for x in datas.items()])})
    '''


@write
def delete(min_date=None, risk: PortfoliosRisk = None):
    if min_date is None and risk is None:
        return 'truncate table robo_hold_portfolios'
    else:
        sql = f"rhp_date >= '{format_date(min_date)}'" if min_date else None
        return f"delete from robo_hold_portfolios {where(sql, rhp_risk=risk)}"