import datetime
import pandas as pd
import re
from env import DAILY_DIR
from collateral.baml_isda import baml_load_excel


def gs_navs(date: datetime.date = None):
    d = {}
    date_str = date.strftime("%d_%b_%Y") if date else ""
    for fname in (DAILY_DIR / "GS_reports").glob(f"Trade_Detail*{date_str}*.xls"):
        try:
            df = pd.read_excel(fname, skiprows=9, skipfooter=77, index_col="Trade Id")
        except ValueError:
            continue
        df = df.dropna(subset=["GS Entity"])
        df["Trade Date"] = pd.to_datetime(df["Trade Date"])
        df = df[["Trade Date", "Buy/Sell", "Notional (USD)", "NPV (USD)"]]
        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
        name = fname.name.replace("9972734", "")
        if m := re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name):
            date_string, = m.groups()
            date = datetime.datetime.strptime(date_string, "%d_%b_%Y")
        d[date] = df
    df = pd.concat(d)
    # nav is from Goldman's point of view
    df.nav *= -1.0
    return df


def ms_navs(date: datetime.date = None):
    d = {}
    date_str = date.strftime("%Y%m%d") if date else "*"
    for fname in (DAILY_DIR / "MS_reports").glob(f"Trade_Detail_{date_str}.xls"):
        df = pd.read_excel(fname, index_col="trade_id")
        df.trade_date = pd.to_datetime(df.trade_date)
        df = df[
            ["trade_date", "pay_rec", "notional_in_trade_ccy", "exposure_in_rpt_ccy"]
        ]
        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
        if m := re.match(r"[^\d]*(\d{8})", fname.name):
            date_string, = m.groups()
            date = datetime.datetime.strptime(date_string, "%Y%m%d")
        d[date] = df
    return pd.concat(d)


def citi_navs(date: datetime.date = None):
    d = {}
    glob_str = date.strftime("%Y%m%d*") if date else "*"
    for fname in (DAILY_DIR / "CITI_reports").glob(f"262966_Portfolio_{glob_str}.xlsx"):
        date_parsed = datetime.datetime.strptime(
            fname.stem.rsplit("_", 1)[1][:-3], "%Y%m%d%H%M%S%f"
        )
        df = pd.read_excel(
            fname, skiprows=6, skipfooter=2, parse_dates=["Trade Date", "Value Date"]
        )
        df = df.dropna(subset=["Operations File"]).set_index(
            ["Value Date", "Operations File"]
        )
        df = df[["Trade Date", "Party Position", "Notional", "Market Value"]]
        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
        d[date_parsed] = df
    # there can be multiple files per day, we take the latest one
    df = (
        pd.concat(d)
        .sort_index()
        .groupby(level=["Value Date", "Operations File"])
        .last()
    )
    # nav is from Citi's point of view
    df.nav *= -1.0
    return df


def baml_navs(date: datetime.date = None):
    d = {}
    glob_str = date.strftime("%d-%b-%Y") if date else "*"
    for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
        f"Interest Rates Trade Summary_{glob_str}.xls"
    ):
        date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
        df = baml_load_excel(fname)
        df = df.set_index("Trade ID")
        df = df[["Trade Date", "Flow Direction", "Notional", "MTM(USD)"]]
        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
        d[date] = df
    return pd.concat(d)


def bnp_navs(date: datetime.date = None):
    d = {}
    date_str = date.strftime("%Y%m%d") if date else ""
    for fname in (DAILY_DIR / "BNP_reports").glob(f"Exposure*{date_str}.XLS"):
        try:
            df = pd.read_excel(fname, skiprows=7)
        except ValueError:
            continue
        df["Trade Ref"] = df["Trade Ref"].str.replace("MBO-", "")
        df = df.set_index("Trade Ref")
        df["Trade Date"] = pd.to_datetime(df["Trade Date"], dayfirst=True)
        df = df[["Trade Date", "Buy/Sell", "Notional 1", "Exposure Amount (Agmt Ccy)"]]
        df.columns = ["trade_date", "buy/sell", "notional", "nav"]
        d[datetime.datetime.strptime(fname.stem[-8:], "%Y%m%d").date()] = df
    df = pd.concat(d)
    # nav is from BNP's point of view
    df.nav *= -1.0
    return df


# def bnp_navs_old(date: datetime.date = None):
#     d = {}
#     date_str = date.strftime("%d%b%Y") if date else ""
#     for fname in (DAILY_DIR / "BNP_reports").glob(f"SERENITAS*0_*{date_str}.csv"):
#         try:
#             df = pd.read_csv(fname)
#         except ValueError:
#             continue
#         df = df.set_index("Contract")
#         df["COB Date"] = pd.to_datetime(df["COB Date"])
#         df = df[["COB Date", "B/S", "Notional", "Reval PV"]]
#         df.columns = ["trade_date", "buy/sell", "notional", "nav"]
#         d[datetime.datetime.strptime(fname.name.split("_")[3], "%d%b%Y").date()] = df
#     df = pd.concat(d)
#     return df

if __name__ == "__main__":
    import argparse
    import logging
    from utils.db import dbconn
    from pandas.tseries.offsets import BDay

    parser = argparse.ArgumentParser()
    parser.add_argument(
        "date",
        type=datetime.datetime.fromisoformat,
        nargs="?",
        default=datetime.date.today(),
    )
    parser.add_argument(
        "-a", "--all", action="store_true", default=False, help="download everything"
    )
    parser.add_argument(
        "-d", "--debug", action="store_true", default=False, help="more verbose logging"
    )
    args = parser.parse_args()
    date = None if args.all else args.date
    logging.basicConfig()
    logger = logging.getLogger("external_marks")
    logger.setLevel(logging.DEBUG if args.debug else logging.INFO)
    for cp in ["MS", "CITI", "GS", "BAML", "BNP"]:
        logger.info(cp)
        if date and cp != "CITI":
            date_arg = (date - BDay()).date()
        else:
            date_arg = date
        try:
            df = globals()[f"{cp.lower()}_navs"](date_arg)
        except ValueError:
            continue
        logger.debug(df)
        with dbconn("dawndb") as conn:
            with conn.cursor() as c:
                for k, v in df[["nav"]].iterrows():
                    c.execute(
                        "INSERT INTO external_marks_deriv "
                        "VALUES(%s, %s, %s, %s) ON CONFLICT DO NOTHING",
                        (*k, float(v), cp),
                    )