import datetime
import os
import pandas as pd
import re
from db import dbconn
from pathlib import Path

DAILY_DIR = Path(os.environ["DAILY_DIR"])


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
    return pd.concat(d)


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)


if __name__ == "__main__":
    for cp in ["MS", "GS"]:
        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),
                    )