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

logger = logging.getLogger(__name__)


def download_files(*args, fund="Serenitas", **kwargs):
    if fund not in ("Serenitas", "Selene"):
        return
    sftp = SftpClient.from_creds("baml_fcm")
    if fund == "Serenitas":
        filters = (lambda s: "Selene" not in s.filename,)
    elif fund == "Selene":
        filters = (lambda s: "Selene" in s.filename,)
    sftp.download_files(
        "outgoing", DAILY_DIR / fund / "BAML_reports", additional_filters=filters
    )


def load_csv(file_type, fund, d, **kwargs):
    if fund == "Serenitas":
        tag = "LMCG"
    else:
        tag = "Selene"
    return pd.read_csv(
        DAILY_DIR
        / fund
        / "BAML_reports"
        / f"OTC_Open_Positions_-_{file_type}_-_{tag}_{d:%Y%m%d}.CSV",
        **kwargs,
    )


def collateral(d, positions, *, engine, fund="Serenitas", positions_irs, **kwargs):
    df = load_csv(
        "Credit",
        fund,
        d,
        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", fund)
    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"})
    df_rates = load_csv(
        "Rates",
        fund,
        d,
        usecols=[
            "COBDATE",
            "CLEAREDTRADEID",
            "FIXEDRATE",
            "NPV",
            "DIRECTION",
            "NOTIONAL",
            "EFFDATE",
            "MATDATE",
        ],
        index_col=["CLEAREDTRADEID"],
        parse_dates=["MATDATE", "EFFDATE"],
    )
    compare_notionals_rates(df_rates, positions_irs, "BAML")
    positions_irs["Amount"] = df_rates.reindex(positions_irs.index)["NPV"]
    positions_irs.folder.replace(STRATEGY_CASH_MAPPING, inplace=True)
    df_rates = (
        positions_irs.groupby(["folder", "currency"])
        .agg({"Amount": "sum"})
        .reset_index(["folder", "currency"])
    )
    df_rates = df_rates.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",
    ]
    tag = "Selene_" if fund == "Selene" else ""
    df_margin = pd.read_csv(
        DAILY_DIR / fund / "BAML_reports" / f"OTC_Moneyline_{tag}{d:%Y%m%d}.CSV",
        usecols=col_names,
        parse_dates=["Statement Date"],
        index_col=["Statement Date"],
    )
    df_margin_fut = df_margin[df_margin.AT == "R1"]
    df_margin = df_margin[df_margin.AT != "R1"]
    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(numeric_only=True)
    df_margin = df_margin.reset_index()
    df_margin_fut.AT = "USD"
    df_margin_fut = df_margin_fut.reset_index()
    col_names.pop(2)
    accounts = {
        "Serenitas": ("V0NSCLMFCM", "MLNSCLMAFU"),
        "Selene": ("6MZ20K79", ""),
    }
    df_margin["account"], df_margin_fut["account"] = accounts[fund]
    col_names.insert(1, "account")
    try:
        place_holders = ",".join(["%s"] * len(col_names))
        with engine.connect() as conn:
            conn.execute(
                f"INSERT INTO fcm_moneyline VALUES({place_holders})",
                list(df_margin[col_names].itertuples(index=False)),
            )
            conn.execute(
                f"INSERT INTO fcm_moneyline VALUES({place_holders})",
                list(df_margin_fut[col_names].itertuples(index=False)),
            )
    except IntegrityError as e:
        logger.debug(str(e))
        pass
    df = pd.concat([df, df_rates])
    df = (
        df.groupby(["Strategy", "Currency"])
        .agg({"Amount": "sum"})
        .reset_index(["Strategy", "Currency"])
    )
    df["date"] = d
    return df.set_index("Strategy")