import pandas as pd

from . import DAILY_DIR, SftpClient
from .common import compare_notionals, STRATEGY_CASH_MAPPING


def collateral(d, positions, *, engine, **kwargs):
    df = pd.read_csv(
        DAILY_DIR
        / "BowdSt"
        / "GS_fcm_reports"
        / f"Open _Trades_Report_LMCG_51341_{d:%Y%m%d}.csv",
        usecols=[
            "Notional",
            "Direction",
            "NPV (local)",
            "Maturity Date",
            "COB Date",
            "Red Code",
        ],
        parse_dates=["COB Date", "Maturity Date"],
        index_col=["Red Code", "Maturity Date"],
        thousands=",",
    )
    df.Notional = df.Notional.where(df.Direction == "Buy", -df.Notional)
    df.index.names = ["security_id", "maturity"]
    df = df.groupby(level=["security_id", "maturity"])[
        ["Notional", "NPV (local)"]
    ].sum()
    df = df.rename(columns={"Notional": "NOTIONAL"})
    compare_notionals(df, positions, "GS")
    positions["Amount"] = df.reindex(positions.index)["NPV (local)"]
    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
        / "BowdSt"
        / "GS_fcm_reports"
        / f"Account_Balances_and_Margin_Report_LMCG_51338_{d:%Y%m%d}.csv",
        parse_dates=["COB Date"],
        thousands=",",
    )

    df_margin = df_margin.rename(
        columns={
            "Opening Balance (local)": "beginning_balance",
            "Ending Balance (local)": "ending_balance",
            "PAI (local)": "pai",
            "Account Value (local)": "account_value_market",
            "Initial Margin Requirement (local)": "current_im",
            "Excess/Deficit (local)": "current_excess_deficit",
            "Currency": "currency",
            "GS Account Number": "account",
            "COB Date": "date",
        }
    )
    cols = [
        "date",
        "account",
        "beginning_balance",
        "ending_balance",
        "pai",
        "account_value_market",
        "current_im",
        "current_excess_deficit",
        "currency",
    ]
    place_holders = ",".join(["%s"] * len(cols))
    engine.execute(
        f"INSERT INTO fcm_moneyline({','.join(cols)}) VALUES({place_holders})",
        list(df_margin[cols].itertuples(index=False)),
    )
    df["date"] = d
    return df.set_index("Strategy")