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))