from . import DAILY_DIR, SftpClient
from .common import compare_notionals, STRATEGY_CASH_MAPPING
import pandas as pd
from sqlalchemy.exc import IntegrityError


def download_files(*args, **kwargs):
    sftp = SftpClient.from_creds("baml_fcm")
    sftp.download_files("outgoing", DAILY_DIR / "BAML_reports")


def collateral(d, positions, *, engine, **kwargs):
    df = pd.read_csv(
        DAILY_DIR
        / "BAML_reports"
        / f"OTC_Open_Positions_-_Credit_-_LMCG_{d:%Y%m%d}.CSV",
        usecols=[
            "MTM",
            "ACCRUEDCPN",
            "VARMARGIN",
            "REDCODE",
            "NOTIONAL",
            "EODSETTLEMENTPRICE",
            "PERIOD",
            "BUYSELL",
        ],
        index_col=["REDCODE"],
    )
    df.PERIOD = pd.to_datetime(df.PERIOD.astype("str") + "20")
    df = df.set_index("PERIOD", append=True)
    df = df[df.EODSETTLEMENTPRICE.notnull()]
    df["NOTIONAL"] = df.NOTIONAL.where(df.BUYSELL == "Buy", -df.NOTIONAL).astype(
        "float"
    )
    df["DIRTYUPFRONT"] = (df.MTM + df.ACCRUEDCPN) / df.NOTIONAL
    df.index.names = ["security_id", "maturity"]
    # dropped old versions (trades should be offsetting
    df = df.groupby(level=["security_id", "maturity"]).agg(
        {"NOTIONAL": "sum", "DIRTYUPFRONT": "first"}
    )
    df = df[df.NOTIONAL != 0.0]
    compare_notionals(df, positions, "BAML")
    positions["dirtyupfront"] = df.reindex(positions.index)["DIRTYUPFRONT"]
    positions["Amount"] = positions["notional"] * positions["dirtyupfront"]
    positions.folder = positions.folder.replace(STRATEGY_CASH_MAPPING)

    def aux(row):
        if row.folder == "XCURVE":
            return "SER_IGCVECSH" if row.currency == "USD" else "SER_ITRXCVCSH"
        else:
            return row.folder

    positions.folder = positions.apply(aux, axis=1)
    df = (
        positions.groupby(["folder", "currency"])
        .agg({"Amount": "sum"})
        .reset_index(["folder", "currency"])
    )
    df = df.rename(columns={"folder": "Strategy", "currency": "Currency"})
    col_names = [
        "Statement Date",
        "AT",
        "AT CCY",
        "Beginning Balance",
        "CDS Initial Coupon",
        "CDS Trade Variation",
        "Price Aligned Interest",
        "Clearing Fee",
        "Commission",
        "Cash Amounts",
        "Ending Balance",
        "Account Value at Market",
        "Net P&L",
        "Initial Margin Requirement",
        "Margin Excess/Deficit",
    ]
    df_margin = pd.read_csv(
        DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV",
        usecols=col_names,
        parse_dates=["Statement Date"],
        index_col=["Statement Date"],
    )
    df_margin.AT = df_margin.AT.replace(
        {"Q4": "EUR", "F4": "EUR", "Q1": "USD", "F2": "USD", "9Z": "ZZZZZ",}
    )
    df_margin = df_margin.set_index("AT", append=True)
    df_margin = df_margin.groupby(level=(0, 1)).sum()
    df_margin = df_margin.reset_index()
    col_names.pop(2)
    try:
        place_holders = ",".join(["%s"] * (len(col_names) - 1))
        engine.execute(
            f"INSERT INTO fcm_moneyline VALUES(%s, 'V0NSCLMFCM', {place_holders})",
            list(df_margin[col_names].itertuples(index=False)),
        )
    except IntegrityError:
        pass
    df["date"] = d
    return df.set_index("Strategy")