from analytics import Portfolio, DualCorrTranche
import logging

logger = logging.getLogger(__name__)

def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST"):
    if by_strat:
        sql_string = "SELECT * from list_tranche_positions_by_strat(%s, %s)"
        params = (date, fund)
    else:
        sql_string = ("SELECT folder, id from cds "
                      "WHERE orig_attach IS NOT NULL "
                      "AND (termination_date IS NULL OR termination_date > %s) "
                      "AND maturity > %s AND fund = %s ORDER BY trade_date")
        params = (date, date, fund)
    with conn.cursor() as c:
        c.execute(sql_string, params)
        trade_ids = list(c)
    if by_strat:
        portf = Portfolio([DualCorrTranche(redcode=d['security_id'],
                                           maturity=d['maturity'],
                                           notional=d['notional'],
                                           tranche_running=d['fixed_rate']*100,
                                           attach=d['orig_attach'],
                                           detach=d['orig_detach'],
                                           corr_attach=None, corr_detach=None)
                           for d in trade_ids])
        portf.trade_ids = [(d["folder"],
                             f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}")
                            for d, t in zip(trade_ids, portf.trades)]
    else:
        portf = Portfolio([DualCorrTranche.from_tradeid(dealid) for _, dealid in trade_ids],
                          trade_ids)
    portf.value_date = date
    portf.mark()
    return portf

def insert_tranche_marks(portf, conn):
    cols = ["clean_nav", "accrued", "theta", "duration", "delta",
            "upfront", "running", "index_refprice", "index_refspread",
            "index_duration"]
    update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols)
    sql_str = (f"INSERT INTO tranche_risk VALUES({','.join(['%s'] * 12)}) "
               " ON CONFLICT (date, tranche_id) DO UPDATE "
               f"SET {update_str}")
    with conn.cursor() as c:
        for trade_id, trade in portf.items():
            c.execute(sql_str, (trade.value_date,
                                trade_id,
                                trade.clean_pv,
                                -trade._accrued * trade.notional,
                                None,
                                trade.duration,
                                trade.delta,
                                trade.upfront,
                                trade.tranche_running,
                                100 - float(trade._index.pv()) * 100,
                                trade._index._snacspread(trade._index.coupon(),
                                                         trade._index.recovery,
                                                         trade._index.maturities[0]) * 10000,
                                float(trade._index.duration()))
            )
    conn.commit()