from serenitas.analytics import Portfolio, DualCorrTranche
import logging

logger = logging.getLogger(__name__)


def get_tranche_portfolio(date, conn, by_strat=False, fund="SERCGMAST", **kwargs):
    if by_strat:
        sql_string = "SELECT * from list_tranche_positions_by_strat(%s, %s)"
    else:
        sql_string = (
            "SELECT * FROM list_cds(%s, %s) "
            "WHERE orig_attach IS NOT NULL "
            "ORDER BY security_desc, attach"
        )

    with conn.cursor() as c:
        c.execute(sql_string, (date, fund))
        trade_ids = list(c)

    portf = Portfolio(
        [
            DualCorrTranche(
                redcode=t.security_id,
                maturity=t.maturity,
                notional=t.notional,
                tranche_running=t.fixed_rate * 100,
                attach=t.orig_attach,
                detach=t.orig_detach,
                corr_attach=None,
                corr_detach=None,
                value_date=t.trade_date,
                trade_id=t.id,
            )
            for t in trade_ids
        ]
    )
    if by_strat:
        portf.trade_ids = [
            (tid.folder, f"{t.index_type} {t.series} {t.tenor} {t.attach}-{t.detach}")
            for tid, t in zip(trade_ids, portf.trades)
        ]
    else:
        portf.trade_ids = [(t.folder, t.id) for t in trade_ids]
    portf.value_date = date
    portf.mark(**kwargs)
    return portf


def insert_tranche_portfolio(portf, conn):
    cols = [
        "date",
        "tranche_id",
        "notional",
        "clean_nav",
        "accrued",
        "duration",
        "delta",
        "gamma",
        "theta",
        "theta_amount",
        "corr01",
        "tranche_factor",
        "upfront",
        "running",
        "corr_attach",
        "corr_detach",
        "index_refprice",
        "index_refspread",
        "index_duration",
        "hy_equiv",
    ]
    update_str = ",".join(f"{c} = EXCLUDED.{c}" for c in cols[2:])
    sql_str = (
        f"INSERT INTO tranche_risk({','.join(cols)}) "
        f"VALUES({','.join(['%s'] * len(cols))}) "
        " ON CONFLICT (date, tranche_id) DO UPDATE "
        f"SET {update_str}"
    )

    with conn.cursor() as c:
        for (strat, trade_id), trade in portf.items():
            logger.info(f"marking tranche {trade_id} in {strat}")
            try:
                theta = trade.theta(method="TLP")
            except (ValueError, RuntimeError) as e:
                # when there is less than one year left we computed the theta to maturity
                logger.info(str(e))
                theta = (
                    trade.clean_pv
                    / trade.notional
                    / trade.tranche_factor
                    / trade._index._fx
                    + trade.tranche_running * 1e-4 * trade.duration
                )
            c.execute(
                sql_str,
                (
                    trade.value_date,
                    trade_id,
                    trade.notional,
                    trade.clean_pv,
                    trade.accrued,
                    trade.duration,
                    trade.delta,
                    trade.gamma,
                    theta,
                    -theta * trade.notional * trade.tranche_factor * trade._index._fx,
                    trade.corr01,
                    trade.tranche_factor,
                    trade.upfront,
                    trade.tranche_running,
                    trade.rho[0],
                    trade.rho[1],
                    100 - float(trade._index.pv()) * 100,
                    trade._index._snacspread(
                        trade._index.coupon(), trade._index.recovery, trade.maturity
                    )
                    * 10000,
                    float(trade._index.duration()),
                    trade.hy_equiv,
                ),
            )
    conn.commit()