import datetime
import pandas as pd
import pathlib
from exchangelib import FileAttachment
from io import StringIO
from typing import Tuple
from serenitas.analytics.dates import bus_day
from serenitas.utils.db import dbconn, dawn_engine
from serenitas.utils.env import DAILY_DIR


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")
                    or fname.startswith("Net Investment")
                    or fname.startswith("Settled Cash")
                ):
                    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_dir(workdate: datetime.date) -> pathlib.Path:
    p = DAILY_DIR / str(workdate) / "Reports"
    if not p.exists():
        p = (
            DAILY_DIR
            / str(workdate.year)
            / f"{workdate:%Y_%m}"
            / str(workdate)
            / "Reports"
        )
    return p


def load_val_report(workdate: datetime.date):
    p = get_dir(workdate) / 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",
            "Accounting Status",
            "Primary GSP Account",
            "Extended GSP Account Number",
            "Percent Of Total",
        ],
        axis=1,
    )
    if "Acctg Status Update (EDT)" in df:
        del df["Acctg Status Update (EDT)"]
    elif "Acctg Status Update (EST)" in df:
        del df["Acctg Status Update (EST)"]
    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",
    ]:
        if df[col].dtype != "float64":
            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 load_pnl_report(workdate: datetime.date):
    if workdate.weekday() == 0:
        workdate -= datetime.timedelta(days=2)
    p = (
        get_dir(workdate)
        / f"Net Investment Earned Income by Security_{workdate:%d %b %Y}.csv"
    )
    df = pd.read_csv(p, thousands=",", parse_dates=["Begin Date", "End Date"])
    df = df.drop(
        [
            "Reporting Account Number",
            "Reporting Account Name",
            "Reporting Account Base Currency",
            "Accounting Status",
            "Security Cross Reference Type",
            "Security Cross Reference Cusip",
            "Local Currency Description",
            "Country Of Issue",
            "Country Of Issue Description",
            "State Code",
            "Asset Type Code",
            "5500 category code",
            "5500 class code Description",
            "CINS",
            "SEDOL",
            "Valoren",
            "Sicovam",
            "WPK",
            "QUICK",
            "Underlying Sec ID",
            "Loan ID",
            "Counterparty",
            "Source Account Name",
            "Source Account Number",
            "Fair Value Hierarchy - Beginning of Period",
            "Fair Value Override - Beginning of Period",
            "Fair Value Hierarchy - End of Period",
            "Fair Value Override - End of Period",
            "Country of Registration name",
            "Country of Registration code",
        ],
        axis=1,
    )
    df.columns = df.columns.str.replace(" ", "_").str.lower()
    df = df.rename(
        columns={
            "change_in_unrealized_currency_gain_loss": "unrealized_currency",
            "change_in_unrealized_investment_gain_loss": "unrealized_investment",
            "total_change_in_unrealized_gain_loss": "total_unrealized",
            "accretion/amortization": "accretion_amortization",
            "journal_entry_accretion/amortization": "journal_entry_accretion_amortization",
            "realized_gain/loss": "realized_gain_loss",
            "journal_entry_realized_g/l": "journal_entry_realized_gl",
            "manager": "link_ref",
            "realized_gain/loss_investment": "realized_investment",
            "realized_gain/loss_currency": "realized_currency",
            "realized_gain/loss_settled": "realized_settled",
            "realized_gain/loss_traded": "realized_traded",
        }
    )
    for col in [
        "unrealized_currency",
        "unrealized_investment",
        "total_unrealized",
        "ending_shares_par",
        "opening_receivables",
        "closing_receivables",
        "income",
        "journal_entry_income",
        "accretion_amortization",
        "journal_entry_accretion_amortization",
        "realized_gain_loss",
        "journal_entry_realized_gl",
        "realized_loss_impaired_securities",
        "net_investment_income",
        "realized_investment",
        "realized_currency",
        "realized_settled",
        "realized_traded",
    ]:
        if df[col].dtypes == "object":
            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_pnl", 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 load_cash_report(workdate: datetime.date):
    p = (
        DAILY_DIR
        / str(workdate)
        / "Reports"
        / f"Settled Cash Statement_{workdate:%d %b %Y}.csv"
    )
    df = pd.read_csv(p, thousands=",")
    df = df[
        df["Transaction Type"].isna() | df["Transaction Type"].isin(["BUY", "SELL"])
    ]
    df = df.groupby(["Account Name", "Account Number", "Local Currency Code"]).sum()
    df["date"] = workdate
    df["fund"] = "BOWDST"
    df = df[["Opening Balance Local", "date", "fund"]]
    df.reset_index(inplace=True)
    df["Account Number"] = df["Account Number"].astype(
        "int64"
    )  # Account Numbers are read in as float
    df = df.rename(
        {
            "Account Name": "account_name",
            "Account Number": "account_number",
            "Local Currency Code": "currency_code",
            "Opening Balance Local": "balance",
        },
        axis=1,
    )
    df.to_sql("cash_balances", dawn_engine, if_exists="append", index=False)


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",
            "caagprim@bnymellon.com",
        ),
        cc_recipients=("bowdoin-ops@lmcg.com",),
        attach=attachments,
    )


if __name__ == "__main__":
    import argparse
    from serenitas.utils.exchange import ExchangeMessage

    parser = argparse.ArgumentParser()
    parser.add_argument(
        "workdate",
        nargs="?",
        type=datetime.date.fromisoformat,
        default=datetime.date.today(),
        help="working date",
    )
    args = parser.parse_args()
    em = ExchangeMessage()
    download_messages(em)
    cob = (args.workdate - bus_day).date()
    df_bonds, df_cds, df_tranches = get_positions(cob)
    send_email(em, cob, df_bonds, df_cds, df_tranches)
    load_cash_report(args.workdate)
    load_val_report(args.workdate)
    load_pnl_report(args.workdate)