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

from api import PortfoliosRisk, PortfoliosType

__COLUMNS__ = {
    'rmp_id': 'id',
    'rmp_date': 'date',
    'rmp_risk': 'risk',
    'rmp_type': 'type',
    'rmp_rolve': 'solve',
    'rmp_portfolio': 'portfolio',
    'rmp_cvar': 'cvar',
}


@write
def insert(datas):
    datas = mapper_columns(datas=datas, columns=__COLUMNS__)
    return f'''
    insert into robo_mpt_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_mpt_portfolios'
    else:
        sql = f"rmp_date >= '{format_date(min_date)}'" if min_date else None
        return f"delete from robo_mpt_portfolios {where(sql, rmp_risk=risk)}"


@read(one=True)
def get_one(day, type: PortfoliosType, risk: PortfoliosRisk):
    return f'''
    select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_mpt_portfolios
    {where(rmp_date=day, rmp_risk=risk, rmp_type=type)} 
    '''


@read
def get_list(max_date=None, min_date=None, type: PortfoliosType = None, risk: PortfoliosRisk = None):
    sqls = []
    if max_date:
        sqls.append(f"rmp_date <= '{format_date(max_date)}'")
    if min_date:
        sqls.append(f"rmp_date >= '{format_date(min_date)}'")
    return f'''
    select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_mpt_portfolios
    {where(*sqls, rmp_risk=risk, rmp_type=type)}
    '''