from serenitas.utils.db import dbconn
from serenitas.utils.exchange import ExchangeMessage, FileAttachment
from serenitas.utils.env import DAILY_DIR
from exchangelib import HTMLBody
import datetime
import argparse
from pandas.tseries.offsets import BDay
from io import StringIO
import pandas as pd
from premailer import transform
from collateral.common import load_pdf, get_col
from collateral.citi import get_df


def html_generator(df, column_name):
    formatters = {"excess": "{:,.0f}".format, "receive": "{:,.0f}".format}
    return transform(
        df.style.format(formatter=formatters, thousands=",")
        .set_table_attributes('border="1"')
        .applymap(lambda x: "text-align: right;", subset=[column_name])
        .hide_index()
        .render()
    )


def get_total_collateral_citi(d):
    try:
        fname = next(
            (DAILY_DIR / "CITI_reports").glob(
                f"262966_MarginNotice_{d.strftime('%Y%m%d')}_*.pdf"
            )
        )
    except StopIteration:
        raise FileNotFoundError(f"CITI file not found for date {d.date()}")
    l = load_pdf(fname)
    col1 = (370, 500, 70, 250)
    col2 = (370, 500, 300, 530)
    col3 = (370, 500, 530, 600)
    variation_margin = get_df(l, col1, col2, col3)
    anchor = next(c for c in l if c.text == "Non Regulatory Initial Margin")
    top = int(anchor["top"]) + 10
    bottom = top + 160
    col1 = (top, bottom, 70, 320)
    col2 = (top, bottom, 320, 530)
    col3 = (top, bottom, 530, 600)
    initial_margin = get_df(l, col1, col2, col3)
    margins = {
        "vm": [-variation_margin.loc["Regulatory VM Requirement", "amount"]],
        "im": [-initial_margin.loc["Non Reg IM Requirement Due Customer", "amount"]],
    }
    return (
        pd.DataFrame.from_dict(margins, orient="index", columns=["amount to receive"])
        .rename_axis("account")
        .reset_index()
    )


if __name__ == "__main__":
    parser = argparse.ArgumentParser()
    parser.add_argument(
        "trade_date",
        nargs="?",
        default=(datetime.date.today() - BDay(1)).date(),
        type=datetime.date.fromisoformat,
    )
    args = parser.parse_args()
    dawndb = dbconn("dawndb")

    cp_mapping = {
        "CITI": "Citi",
        "MS": "Morgan Stanley",
        "GS": "Goldman Sachs",
        "BOMLCM": "Baml FCM",
        "BAML_ISDA": "Baml OTC",
        "WELLS": "Wells Fargo",
        "BNP": "BNP Paribas",
        "CS": "Credit Suisse",
        "JPM": "JP Morgan",
        "WELLSFCM": "Wells Fargo FCM",
    }

    isda_cp = pd.read_sql_query(
        "SELECT date, broker as counterparty, -sum(amount) as excess FROM strategy_im si WHERE strategy='CSH_CASH' GROUP BY broker, date, fund having date=%s and fund='SERCGMAST' ORDER BY date DESC;",
        con=dawndb,
        params=(args.trade_date,),
    )
    isda_cp["counterparty"] = [
        cp_mapping[counterparty] for counterparty in isda_cp["counterparty"]
    ]
    fcm_cp = pd.read_sql_query(
        "SELECT date, custodian AS counterparty, current_excess_deficit as excess FROM fcm_moneyline fm LEFT JOIN accounts ON account=cash_account WHERE date=%s AND fund='SERCGMAST' AND currency='ZZZZZ';",
        con=dawndb,
        params=(args.trade_date,),
    )
    fcm_cp["counterparty"] = [
        cp_mapping[counterparty] for counterparty in fcm_cp["counterparty"]
    ]
    payment_settlements = pd.read_sql_query(
        "SELECT settle_date, name as counterparty, asset_class, currency, payment_amount as receive FROM payment_settlements ps WHERE settle_date between %s and %s and fund='SERCGMAST' order by settle_date asc;",
        con=dawndb,
        params=((args.trade_date + BDay(1)).date(), (args.trade_date + BDay(3)).date()),
    )
    payment_settlements_agg = pd.read_sql_query(
        "SELECT settle_date , currency, sum(payment_amount) as receive FROM payment_settlements WHERE fund= 'SERCGMAST' AND settle_date BETWEEN %s AND %s GROUP BY settle_date, currency order by settle_date asc;",
        con=dawndb,
        params=((args.trade_date + BDay(1)).date(), (args.trade_date + BDay(3)).date()),
    )
    citi = get_total_collateral_citi(args.trade_date)
    body = [
        "<html><body>",
        "<h3> Collateral Estimates Receive/(Pay) at ISDA :</h3>",
        html_generator(isda_cp, "excess"),
        "<h3> Collateral Estimates Receive/(Pay) at FCM :</h3>",
        html_generator(fcm_cp, "excess"),
        "<h3>Payment Settlements By Date :</h3>",
        html_generator(payment_settlements_agg, "receive"),
        "<h3>Payment Settlements :</h3>",
        html_generator(payment_settlements, "receive"),
        "<h3>Citi Breakdown :</h3>",
        html_generator(citi, "amount to receive"),
        "</body></html>",
    ]

    em = ExchangeMessage()
    em.send_email(
        f"Collateral Estimates {args.trade_date:%Y-%m-%d}",
        HTMLBody("".join(body)),
        ["NYOps@lmcg.com"],
        ["fyu@lmcg.com"],
    )