import logging

from serenitas.analytics.api import Portfolio, BlackSwaption
from serenitas.utils.db2 import DataError
from psycopg import sql

logger = logging.getLogger(__name__)


def get_swaption_portfolio(date, conn, fund="SERCGMAST", portfolio=None, **kwargs):
    if portfolio is None:
        params = (date, date, date, fund)
        and_clause = ""
    else:
        params = (date, date, date, fund, portfolio)
        and_clause = "AND portfolio=%s"
    with conn.cursor() as c:
        c.execute(
            "SELECT swaptions.id, folder, dealid, (notional - terminated_amount) AS notional "
            "FROM swaptions LEFT JOIN ("
            "SELECT dealid, SUM(termination_amount) AS terminated_amount "
            "FROM terminations WHERE termination_date <= %s GROUP BY dealid) b "
            "USING (dealid) "
            "WHERE notional IS DISTINCT FROM terminated_amount "
            "AND expiration_date > %s AND trade_date <= %s "
            "AND swap_type='CD_INDEX_OPTION' "
            f"AND fund=%s {and_clause} "
            "ORDER BY expiration_date, strike",
            params,
        )
        try:
            trade_ids, folders, deal_ids, notionals = zip(*c)
        except ValueError:
            return {}
    portf = Portfolio(
        [BlackSwaption.from_tradeid(t) for t in trade_ids], list(zip(folders, deal_ids))
    )
    for t, ntl in zip(portf.trades, notionals):
        if ntl is not None:
            t.notional = ntl
    portf.value_date = date
    portf.mark(interp_method="bivariate_linear", **kwargs)
    return portf


def insert_swaption_portfolio(portf, conn, overwrite=True):
    columns = ["market_value", "delta", "gamma", "vega", "theta", "hy_equiv"]
    place_holders = sql.SQL(", ").join([sql.Placeholder()] * 8)
    if overwrite:
        update_str = sql.SQL("DO UPDATE SET {}").format(
            sql.SQL(", ").join(
                sql.SQL("{} = excluded.{}").format(
                    sql.Identifier(col), sql.Identifier(col)
                )
                for col in columns
            )
        )
    else:
        update_str = sql.SQL("DO NOTHING")
    sql_str = sql.SQL(
        "INSERT INTO swaption_marks VALUES({}) ON CONFLICT (dealid, date) {} "
    ).format(place_holders, update_str)
    with conn.cursor() as c:
        for id, trade in portf.items():
            to_insert = (
                id[1],
                trade.value_date,
                trade.pv,
                trade.delta,
                trade.gamma,
                trade.vega,
                trade.theta,
                trade.hy_equiv,
            )
            try:
                c.execute(sql_str, to_insert)
            except DataError as e:
                logger.error(e)
            finally:
                logger.info("succesfully marked trade id: %s", id)
    conn.commit()