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 trade_date <= %s "
            "AND fund = %s ORDER BY trade_date"
        )
        params = (date, date, date, fund)
    with conn.cursor() as c:
        c.execute(sql_string, params)
        trade_ids = [tuple(e) for e in c]
    if by_strat:
        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,
                )
                for t in trade_ids
            ]
        )
        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 = Portfolio(
            [DualCorrTranche.from_tradeid(dealid) for _, dealid in trade_ids], trade_ids
        )
    portf.value_date = date
    portf.mark()
    return portf


def insert_tranche_portfolio(portf, conn):
    cols = [
        "clean_nav",
        "accrued",
        "duration",
        "delta",
        "gamma",
        "theta",
        "tranche_factor",
        "upfront",
        "running",
        "corr_attach",
        "corr_detach",
        "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'] * 16)}) "
        " 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:
                theta = None
            c.execute(
                sql_str,
                (
                    trade.value_date,
                    trade_id,
                    trade.clean_pv,
                    -trade._accrued * trade.notional,
                    trade.duration,
                    trade.delta,
                    trade.gamma,
                    theta,
                    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()),
                ),
            )
    conn.commit()