import datetime
import pandas as pd
import re
from env import DAILY_DIR
from utils.db import dbconn


def gs_navs():
    d = {}
    for fname in (DAILY_DIR / "GS_reports").glob("Trade_Detail*.xls"):
        try:
            df = pd.read_excel(fname, skiprows=9, skipfooter=77, index_col="Trade Id")
        except ValueError:
            continue
        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", "")
        m = re.match(r"[^\d]*(\d{2}_.{3}_\d{4})", name)
        if m:
            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():
    d = {}
    for fname in (DAILY_DIR / "MS_reports").glob("Trade_Detail*.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"]
        m = re.match(r"[^\d]*(\d{8})", fname.name)
        if m:
            date_string, = m.groups()
            date = datetime.datetime.strptime(date_string, "%Y%m%d")
        d[date] = df
    return pd.concat(d)


def citi_navs():
    l = []
    for fname in (DAILY_DIR / "CITI_reports").glob("262966_Portfolio_*.xlsx"):
        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"]
        l.append(df)
    df = pd.concat(l)
    # nav is from Citi's point of view
    df.nav *= -1.0
    return df


def baml_navs():
    d = {}
    for fname in (DAILY_DIR / "BAML_ISDA_reports").glob(
        "Interest Rates Trade Summary_*.xls"
    ):
        date = datetime.datetime.strptime(fname.stem.split("_")[1], "%d-%b-%Y")
        df = pd.read_excel(fname, skiprows=6, nrows=1)
        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)


if __name__ == "__main__":
    for cp in ["MS", "CITI", "GS", "BAML"]:
        df = globals()[f"{cp.lower()}_navs"]()
        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),
                    )