import datetime
import pandas as pd
import pathlib
import subprocess
from . import DAILY_DIR
from bs4 import BeautifulSoup
from operator import itemgetter
from pandas.tseries.offsets import BDay
from xlrd import open_workbook


def download_files(count=20):
    from exchange import ExchangeMessage

    DATA_DIR = DAILY_DIR / "CS_reports"
    em = ExchangeMessage()
    emails = em.get_msgs(
        path=["NYops", "Margin Calls CS"], count=count, subject__contains="DERV048829"
    )
    for msg in emails:
        for attach in msg.attachments:
            fname = attach.name
            if fname.endswith("xlsx"):
                p = DATA_DIR / fname
                if not p.exists():
                    p.write_bytes(attach.content)

    emails = em.get_msgs(
        path=["NYops", "Margin Calls CS"],
        count=count,
        sender="americas.collateralmgt@credit-suisse.com",
    )

    for msg in emails:
        for attach in msg.attachments:
            fname = attach.name
            if "Serenitas CGMF" in fname:
                p = DATA_DIR / fname
                p = p.parent / f"{msg.datetime_sent:%Y-%m-%d} {p.stem}{p.suffix}"
                if not p.exists():
                    p.write_bytes(attach.content)


def load_pdf(file_path: pathlib.Path):
    proc = subprocess.run(
        ["pdftohtml", "-xml", "-l", "1", "-stdout", "-i", file_path.as_posix()],
        capture_output=True,
    )
    soup = BeautifulSoup(proc.stdout, features="lxml")
    l = soup.findAll("text")
    for e in l:
        if e.text.startswith(
            "Market Value of Collateral required pursuant to this notice"
        ):
            sib = e.next_siblings
            next(sib)
            return float(next(sib).text.replace(",", ""))


def get_collateral(d):
    DATA_DIR = DAILY_DIR / "CS_reports"
    # get most recent file before current date
    def get_date(p):
        return datetime.date.fromisoformat(p.stem.split(" ", 1)[0])

    files = ((f, get_date(f)) for f in DATA_DIR.glob("*.xls"))
    files = sorted(filter(lambda t: t[1] <= d, files), key=itemgetter(1), reverse=True)
    excel_file, date = files[0]
    pdf_file = DATA_DIR / f"{date} Margin_Notice161 Serenitas CGMF RVM.pdf"
    last_margin_call = load_pdf(pdf_file)
    if date == d:  # margin call is current do not include
        last_margin_call = 0
    wb = open_workbook(files[0][0])
    s = wb.sheet_by_index(0)
    i = 0
    im = 0.0
    for i, v in enumerate(s.col_values(0)):
        if s.cell_value(i, 4) == "Total IM (USD):":
            im = s.cell_value(i, 5)
        if v.startswith("Total Value of Collateral"):
            return s.cell_value(i, 1) + last_margin_call + im
        if v.startswith("No Positions to Report"):
            return 0.0


def collateral(d, dawn_trades, *args):
    collateral = get_collateral(d + BDay())
    df = pd.read_excel(
        f"/home/serenitas/Daily/CS_reports/DERV048829_{d:%b%d%Y}.xlsx",
        header=9,
        skipfooter=50,
    )
    df = df[["Order No", "Mid Price", "Notional Currency"]]
    df["Mid Price"] = (
        df["Mid Price"]
        .str.replace(",", "")
        .apply(lambda s: -float(s[1:-1]) if s.startswith("(") else float(s))
    )
    df["Order No"] = df["Order No"].astype("str")
    df = df.merge(dawn_trades, how="left", left_on="Order No", right_on="cpty_id")
    missing_ids = df.loc[df.cpty_id.isnull(), "Order No"]
    if not missing_ids.empty:
        raise ValueError(f"{missing_ids.tolist()} not in the database")
    df.ia = df.ia.fillna(0.0)
    df["Amount"] = df.ia + df["Mid Price"]
    df = df[["folder", "Amount", "Notional Currency"]]
    df = df.groupby(["folder", "Notional Currency"], as_index=False).sum()
    df = df.rename(columns={"folder": "Strategy", "Notional Currency": "Currency"})
    df.Amount *= -1
    df = df.append(
        {
            "Strategy": "M_CSH_CASH",
            "Amount": -collateral - df.Amount.sum(),
            "Currency": "USD",
        },
        ignore_index=True,
    )
    df["date"] = d
    return df.set_index("Strategy")