import pandas as pd
from . import DAILY_DIR, SftpClient2
from .common import compare_notionals, STRATEGY_CASH_MAPPING


def download_files(*args, **kwargs):
    sftp = SftpClient2.from_creds("wells")
    sftp.download_files("/RECEIVE/339425_DATO2", DAILY_DIR / "Wells_reports")


def collateral(d, positions, *, engine, **kwargs):
    account = "A5882186"
    file_name = (
        DAILY_DIR
        / "Wells_reports"
        / f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv"
    )
    try:
        df = pd.read_csv(
            file_name,
            usecols=[
                "TENOR",
                "MARKET_VALUE_NPV",
                "PAIR_CLIP",
                "BUY_SELL",
                "NOTIONAL",
                "MATURITY_DATE",
                "TRADE_PRICE",
            ],
            parse_dates=["MATURITY_DATE"],
            index_col=["PAIR_CLIP", "MATURITY_DATE"],
        )
    except ValueError:
        # backpopulated files have a different format...
        df = pd.read_csv(
            file_name,
            usecols=[
                "Tenor",
                "NPV",
                "Reference_Entity_ID",
                "Fixed_Rate_Notional_Buy",
                "Amount",
                "Scheduled_Termination_Date",
            ],
            parse_dates=["Scheduled_Termination_Date"],
            index_col=["Reference_Entity_ID", "Scheduled_Termination_Date"],
        )
        df = df.rename(
            columns={"Tenor": "TENOR", "NPV": "MARKET_VALUE_NPV", "Amount": "NOTIONAL"}
        )
        df["BUY_SELL"] = 1
        df.loc[df.Fixed_Rate_Notional_Buy.isnull(), "BUY_SELL"] = 2
        del df["Fixed_Rate_Notional_Buy"]
    # df = df[df.TRADE_PRICE != 0.0]
    del df["TRADE_PRICE"]
    df["NOTIONAL"] = df.NOTIONAL.where(df.BUY_SELL == 1, -df.NOTIONAL).astype("float")
    df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL
    df.index.names = ["security_id", "maturity"]
    compare_notionals(df, positions, "Wells")
    positions = positions.join(df, how="left")
    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"})
    df_margin = pd.read_csv(
        DAILY_DIR
        / "Wells_reports"
        / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv",
    )
    table_cols = [
        "VALUE_DATE",
        "CURRENCY_NAME",
        "BEGINNING_BALANCE",
        "CDS_INITIAL_COUPON",
        "CDS_RESET_TO_PAR",
        "PAI",
        "CLEARING_FEES",
        "TRANSACTION_FEES",
        "NET_DEP_WITHDRAW",
        "ENDING_BALANCE",
        "ACCOUNT_VALUE_MARKET",
        "REALIZED_PNL",
        "CURRENT_IM",
        "CURRENT_EXCESS_DEFICIT",
    ]
    update_cols = [c.lower() for c in table_cols[2:]]
    if "VALUE_DATE" in df_margin:
        df_margin.VALUE_DATE = pd.to_datetime(df_margin.VALUE_DATE)
        df_margin["CURRENT_EXCESS_DEFICIT"] += df_margin["PENDING_AMOUNT"]
    else:
        df_margin["Value Date"] = pd.to_datetime(df_margin["Value Date"])
        table_cols = [c.replace("_", " ").title() for c in table_cols]
        table_cols[-2] = "Current IM"
        df_margin["Current Excess Deficit"] += df_margin["Pending Amount"]
    place_holders = ",".join(["%s"] * (len(table_cols) - 1))
    # pomme = (f"INSERT INTO fcm_moneyline VALUES(%s, 'WFNSCLMFCM', {place_holders})"
    #          f"ON CONFLICT (date, account, currency) DO UPDATE "
    #          f"({','.join(update_cols)}) = ({','.join(['EXCLUDED.'+c for c in update_cols])}))
    with engine.connect() as conn:
        conn.execute(
            f"INSERT INTO fcm_moneyline VALUES(%s, 'WFNSCLMFCM', {place_holders})"
            f"ON CONFLICT (date, account, currency) DO UPDATE SET "
            f"({','.join(update_cols)}) = ({','.join(['EXCLUDED.'+c for c in update_cols])})",
            list(df_margin[table_cols].itertuples(index=False)),
        )
    df["date"] = d
    return df.set_index("Strategy")