from . import DAILY_DIR
from paramiko import Transport, SFTPClient


def get_sftp_client():
    transport = Transport(("prmssp.amer.sgcib.com", 22))
    transport.connect(username="SerenitasGamma@USA", password="SSqrrLL99")
    return SFTPClient.from_transport(transport)


def download_sftp_files(
    d=None,
    report_types=[
        "OTC_CASH_ACTIVITY",
        "OTC_POSITIONS",
        "OTC_MARGIN",
        "OTC_MARGIN_EX_DEF",
        "OTC_STATEMENT",
    ],
    retry_count=0,
):
    if retry_count > 20:
        return
    DATA_DIR = DAILY_DIR / "SG_reports"
    sftp = get_sftp_client()
    if d is None:
        for f in sftp.listdir("OTC"):
            if f.endswith("OTC_STATEMENT.xls"):
                print(f)
                sftp.get(f"OTC/{f}", localpath=DATA_DIR / f)
            else:
                for report_type in report_types[:-1]:
                    if f.endswith(f"{report_type}.csv"):
                        print(f)
                        sftp.get(f"OTC/{f}", localpath=DATA_DIR / f)
                else:
                    continue

    else:
        file_list = sftp.listdir("OTC")
        for report_type in report_types:
            if report_type == "OTC_STATEMENT":
                f = f"{d:%Y%m%d}_{report_type}.xls"
            else:
                f = f"{d:%Y%m%d}_{report_type}.csv"
            if f not in file_list:
                logger.info("File not here yet, trying again in 500s...")
                logger.info(f"Try count: {retry_count}")
                sleep(500)
                sftp.close()
                download_sftp_files(d, report_types, retry_count + 1)
            else:
                sftp.get(f"OTC/{f}", localpath=DATA_DIR / f)
    sftp.close()


def collateral(d):
    df_activity = pd.read_csv(
        DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv",
        usecols=["Ticket Reference", "Record Type", "Currency", "Amount"],
    )
    df_position = pd.read_csv(
        DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_POSITIONS.csv",
        usecols=["Ticket Reference", "Reference Entity", "Mtm Value"],
    )
    df_activity = df_activity.loc[df_activity["Record Type"] == "VM"].set_index(
        "Ticket Reference"
    )
    df_margin = pd.read_csv(
        DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_MARGIN_EX_DEF.csv",
        usecols=["Currency", "SG IMR"],
    )
    df_position = df_position.set_index("Ticket Reference")
    # expired_trades
    # df_position = df_position.append(
    #     pd.DataFrame({"Reference Entity": 'CDX-NAIGS29V1-5Y', "Mtm Value": 0.},
    #                  index=['T2201711010000A3K20000045561220U']))
    df = df_activity.join(df_position)
    # expired trade (need to figure out how to get them from the report)
    # df.loc['N201811090000A3K215946925849228U1', 'Mtm Value'] = 0.
    # df.loc['N201811090000A3K215946925849228U1', 'Reference Entity'] = 'CDX-NAIGS31V1-5Y'

    df["Collateral"] = df["Mtm Value"] - df["Amount"]
    ref_entity = df["Reference Entity"].str.split("-", expand=True)
    del ref_entity[0]
    ref_entity.columns = ["to_split", "tenor"]
    ref_entity = ref_entity.join(
        ref_entity["to_split"].str.extract("(IG|HY|EUROPE)S(\d+)V(\d+)$", expand=True)
    )
    del ref_entity["to_split"]
    ref_entity.columns = ["tenor", "index_type", "series", "version"]
    ref_entity.index_type[ref_entity.index_type == "EUROPE"] = "EU"
    df = df.join(ref_entity)
    df = df.groupby(["index_type", "series", "tenor"])["Collateral"].sum()
    positions = pd.read_sql_query(
        "SELECT security_desc, folder, notional, currency "
        "FROM  list_cds_positions_by_strat(%s)",
        dawn_engine,
        params=(d.date(),),
    )
    instruments = positions.security_desc.str.split(expand=True)[[1, 3, 4]]
    instruments.columns = ["index_type", "series", "tenor"]
    instruments.series = instruments.series.str.extract("S(\d+)")
    instruments.index_type[instruments.index_type == "EUR"] = "EU"
    positions = positions.join(instruments)
    del positions["security_desc"]
    positions = positions.set_index(["index_type", "series", "tenor"])
    df = positions.join(df)

    def f(g):
        g.Collateral = g.Collateral * g.notional / g.notional.sum()
        return g

    df = df.groupby(level=["index_type", "series", "tenor"]).apply(f)
    df = df.groupby(["folder"]).agg({"Collateral": "sum", "currency": "first"})
    df = df.reset_index("folder")
    df = df.rename(
        columns={"folder": "Strategy", "currency": "Currency", "Collateral": "Amount"}
    )
    df.Strategy = df.Strategy.map(
        {
            "HEDGE_MBS": "MBSCDSCSH",
            "SER_ITRXCURVE": "SER_ITRXCVCSH",
            "SER_IGCURVE": "SER_IGCVECSH",
            "HYOPTDEL": "HYCDSCSH",
            "IGOPTDEL": "IGCDSCSH",
        }
    )
    df_margin["account"] = "SGNSCLMASW"
    df_margin = df_margin.rename(columns={"SG IMR": "amount", "Currency": "currency"})
    df_margin["date"] = d
    try:
        df_margin.to_sql("fcm_im", dawn_engine, if_exists="append", index=False)
    except IntegrityError:
        pass
    df["date"] = d
    return df