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


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 load_report(workdate: datetime.date):

    p = DAILY_DIR / str(workdate) / "Reports" / f"Asset Detail_{workdate:%d %b %Y}.csv"
    df = pd.read_csv(
        p, thousands=",", parse_dates=["As Of Date", "Maturity Date", "Report Run Date"]
    )
    df = df.drop(
        [
            "Reporting Account Number",
            "Reporting Account Name",
            "Source Account Name",
            "Xref Security ID",
            "Country Name",
            "Country Code",
            "Local Currency Name",
            "Acct Base Currency Name",
            "Acct Base Currency Code",
            "CINS",
            "Issuer ID",
            "SEDOL",
            "Valoren",
            "Sicovam",
            "WPK",
            "Quick",
            "Underlying Sec ID",
            "Loan ID",
            "Manager",
            "Book Yield Value",
            "Counterparty",
            "Ticker with Exchange Code",
            "Ticker with Yellow Key",
            "Acctg Status Update (EDT)",
            "Accounting Status",
            "Primary GSP Account",
            "Extended GSP Account Number",
            "Percent Of Total",
        ],
        axis=1,
    )
    df["Source Account Number"] = df["Source Account Number"].str[-4:].astype("int")
    df.columns = df.columns.str.replace(" ", "_").str.lower()
    df = df.rename(
        columns={
            "shares/par": "current_notional",
            "local_unrealized_gain/loss": "local_unrealized_pnl",
            "base_unrealized_gain/loss": "base_unrealized_pnl",
        }
    )
    for col in [
        "current_notional",
        "local_price",
        "base_price",
        "local_cost",
        "base_cost",
        "local_market_value",
        "base_market_value",
        "local_unrealized_pnl",
        "base_unrealized_pnl",
        "local_notional_cost",
        "base_notional_cost",
        "local_notional_value",
        "base_notional_value",
    ]:
        df[col] = df[col].apply(lambda s: s[1:-1] if s.startswith("(") else s)
        df[col] = pd.to_numeric(df[col].str.replace(",", ""))
    df["row"] = df.index
    df.to_sql("bowdst_val", dawn_engine, if_exists="append", index=False)


def cmp_positions(cob: datetime.date, df_blotter: pd.DataFrame) -> pd.DataFrame:
    workdate = (cob + bus_day).date()
    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(",", ""))
    check = df_blotter.join(df)
    return check


def get_positions(
    cob: datetime.date,
) -> Tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]:
    dawndb = dbconn("dawndb")
    df_blotter = pd.read_sql_query(
        "SELECT * FROM risk_positions(%s, NULL, %s)",
        dawndb,
        params=(cob, "BOWDST"),
        index_col=["identifier"],
    )

    cds_positions = pd.read_sql_query(
        "SELECT * FROM list_cds_marks_pre(%s, NULL, %s)",
        dawndb,
        params=(cob, "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=(cob, "BOWDST", cob),
        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} EOD positions",
        "",
        to_recipients=("reconfiles@bnymellon.com", "hm-operations@bnymellon.com"),
        cc_recipients=("bowdoin-ops@lmcg.com",),
        attach=attachments,
    )


if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser()
    parser.add_argument(
        "cob",
        nargs="?",
        type=datetime.date.fromisoformat,
        default=(datetime.date.today() - bus_day).date(),
        help="close of business date",
    )
    args = parser.parse_args()
    em = ExchangeMessage()
    download_messages(em)
    df_bonds, df_cds, df_tranches = get_positions(args.cob)
    send_email(em, args.cob, df_bonds, df_cds, df_tranches)
    # sftp = SftpClient.from_creds("gs")