import datetime
import pandas as pd
from dates import bus_day
from env import DAILY_DIR
from exchange import ExchangeMessage, Message
from exchangelib import FileAttachment
from io import StringIO
from remote import SftpClient
from typing import Tuple
from utils.db import dbconn


def download_messages(em):
    for msg in em.get_msgs(
        20,
        path=["BowdoinOps", "Reports"],
        subject__startswith="Document(s) from Reporting",
    ):
        if msg.sender == "notify@bnymellon.com":
            for attach in msg.attachments:
                fname = attach.name
                if fname.endswith("csv") and fname.startswith("Asset Detail"):
                    date = datetime.datetime.strptime(
                        fname.split("_")[1].split(".")[0], "%d %b %Y"
                    ).date()
                    p = DAILY_DIR / str(date) / "Reports" / fname
                    if not p.parent.exists():
                        p.parent.mkdir(parents=True)
                    if not p.exists():
                        p.write_bytes(attach.content)


def get_positions(
    workdate: datetime.date,
) -> Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]:
    p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv"
    df = pd.read_csv(p, thousands=",")
    df = df[df["Asset Type"] == "FIXED INCOME SECURITIES"]
    df = df.set_index("CUSIP")
    df = df[["Shares/Par", "Base Price", "Local Market Value"]]
    for col in df.select_dtypes(include=["object"]).columns:
        df[col] = df[col].apply(lambda s: s[1:-1] if s.startswith("(") else s)
        df[col] = pd.to_numeric(df[col].str.replace(",", ""))
    dawndb = dbconn("dawndb")
    d = (workdate - bus_day).date()
    df_blotter = pd.read_sql_query(
        "SELECT * FROM risk_positions(%s, NULL, %s)",
        dawndb,
        params=(d, "BOWDST"),
        index_col=["identifier"],
    )
    check = df_blotter.join(df)

    cds_positions = pd.read_sql_query(
        "SELECT * FROM list_cds_marks_pre(%s, NULL, %s)",
        dawndb,
        params=(d, "BOWDST"),
        index_col=["security_id"],
    )
    tranche_positions = pd.read_sql_query(
        "SELECT id, security_id, security_desc, maturity, a.notional, "
        "protection, orig_attach, orig_detach, tranche_factor, clean_nav, "
        "accrued, cp_code, cpty_id from list_cds(%s, %s) a "
        "LEFT JOIN tranche_risk ON id=tranche_id AND date=%s "
        "WHERE orig_attach IS NOT NULL",
        dawndb,
        params=(d, "BOWDST", d),
        index_col=["id"],
    )

    return df_blotter, cds_positions, tranche_positions


def send_email(
    em,
    workdate: datetime.date,
    df_bonds: pd.DataFrame,
    df_cds: pd.DataFrame,
    df_tranches: pd.DataFrame,
):
    attachments = []
    for name, df in zip(("bonds", "cds", "tranches"), (df_bonds, df_cds, df_tranches)):
        buf = StringIO()
        df.to_csv(buf)
        attachments.append(
            FileAttachment(
                name=f"{workdate} {name}.csv", content=buf.getvalue().encode()
            )
        )
        buf.close()
    em.send_email(
        f"{workdate} positions",
        "",
        to_recipients=("reconfiles@bnymellon.com", "hm-operations@bnymellon.com"),
        cc_recipients=("bowdoin-ops@lmcg.com",),
        attach=attachments,
    )


if __name__ == "__main__":
    em = ExchangeMessage()
    download_messages(em)
    workdate = datetime.date.today()
    df_bonds, df_cds, df_tranches = get_positions(workdate)
    send_email(em, workdate, df_bonds, df_cds, df_tranches)
    # sftp = SftpClient.from_creds("gs")