from . import DAILY_DIR
from .common import compare_notionals
from paramiko import Transport, SFTPClient, RSAKey
import os.path
import pandas as pd
from sqlalchemy.exc import IntegrityError


def get_sftp_client():
    transport = Transport(("ftps.b2b.ml.com", 22))
    pkey = RSAKey.from_private_key_file(os.path.expanduser("~/.ssh/id_rsa_lmcg"))
    transport.connect(username="lmcginvs", pkey=pkey)
    return SFTPClient.from_transport(transport)


def download_files(d=None):
    DATA_DIR = DAILY_DIR / "BAML_reports"
    sftp = get_sftp_client()
    for f in sftp.listdir("outgoing"):
        local_file = DATA_DIR / f
        if not local_file.exists():
            sftp.get(f"outgoing/{f}", localpath=DATA_DIR / f)


def collateral(d, positions, engine):
    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(
        {
            "HEDGE_MBS": "MBSCDSCSH",
            "SER_ITRXCURVE": "SER_ITRXCVCSH",
            "SER_IGCURVE": "SER_IGCVECSH",
            "HYOPTDEL": "COCSH",
            "IGOPTDEL": "COCSH",
            "IGINX": "TCSH",
            "HYINX": "TCSH",
            "XOINX": "TCSH",
        }
    )

    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"})
    df_margin = pd.read_csv(
        DAILY_DIR / "BAML_reports" / f"OTC_Moneyline_{d:%Y%m%d}.CSV",
        usecols=["Statement Date", "AT CCY", "Initial Margin Requirement"],
        parse_dates=["Statement Date"],
    )
    df_margin.columns = ["date", "currency", "amount"]
    df_margin["account"] = "V0NSCLMFCM"
    try:
        engine.execute(
            "INSERT INTO fcm_im "
            "VALUES(%(date)s, %(account)s, %(currency)s, %(amount)s)",
            df_margin.iloc[-1].to_dict(),
        )
    except IntegrityError:
        pass
    df["date"] = d
    return df.set_index("Strategy")