import os
import pandas as pd

from db import dbengine
from exchange import get_msgs, get_account
from exchangelib import Mailbox, Message, HTMLBody
from ftplib import FTP
from pathlib import Path
from time import sleep
from pandas.tseries.offsets import BDay

DAILY_DIR = Path(os.environ["DAILY_DIR"])

def download_files(d=None):
    DATA_DIR = DAILY_DIR / "SG_reports"
    with FTP('ftp.newedgegroup.com') as ftp:
        ftp.login('SerenitasGamma@USA', "SSqrrLL99")
        ftp.cwd('OTC')
        if d is None:
            for f in ftp.nlst():
                if f.endswith("csv") and (("OTC_CASH_ACTIVITY" in f) or ("OTC_POSITIONS" in f)):
                    with open(DATA_DIR / f, "wb") as fh:
                        ftp.retrbinary('RETR ' + f, fh.write)
        else:
            i = 0
            while i <= 20:
                i +=1
                file_list = ftp.nlst()
                for report_type in ["OTC_CASH_ACTIVITY", "OTC_POSITIONS"]:
                    f = f"{d:%Y%m%d}_{report_type}.csv"
                    if f not in file_list:
                        sleep(500)
                        break
                    with open(DATA_DIR / f, "wb") as fh:
                        ftp.retrbinary('RETR ' + f, fh.write)
                else:
                    break

def download_emails():
    emails = get_msgs(path=["NYops"], subject_filter="SERCX **Daily")
    DATA_DIR = DAILY_DIR / "MS_reports"
    for msg in emails:
        for attach in msg.attachments:
            if 'NETSwaps' in attach.name:
                fname = attach.name.split("_")[1]
                with open(DATA_DIR / fname, "wb") as fh:
                    fh.write(attach.content)

def sg_collateral(d):
    df_activity = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_CASH_ACTIVITY.csv")
    df_position = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_POSITIONS.csv")
    df_activity = (df_activity.loc[df_activity['Record Type'] == "VM",
                                   ["Ticket Reference", "Record Type", "Currency", "Amount"]].
                   set_index("Ticket Reference"))
    df_position = df_position.set_index("Ticket Reference")[["Reference Entity", "Mtm Value"]]
    # expired_trades
    # df_position = df_position.append(
    #     pd.DataFrame({"Reference Entity": 'CDX-NAIGS29V1-5Y', "Mtm Value": 0.},
    #                  index=['T2201711010000A3K20000045561220U']))
    df = df_activity.join(df_position)
    df['Collateral'] = df['Mtm Value'] - df['Amount']
    ref_entity = df['Reference Entity'].str.split("-", expand=True)
    del ref_entity[0]
    ref_entity.columns = ['to_split', 'tenor']
    ref_entity = ref_entity.join(ref_entity['to_split'].str.
                                 extract("(IG|HY|EUROPE)S(\d+)V(\d+)$",
                                         expand=True))
    del ref_entity['to_split']
    ref_entity.columns = ['tenor', 'index_type', 'series', 'version']
    ref_entity.index_type[ref_entity.index_type == "EUROPE"] = "EU"
    df = df.join(ref_entity)
    df = df.groupby(['index_type', 'series', 'tenor'])['Collateral'].sum()
    positions = pd.read_sql_query("SELECT security_desc, folder, notional, currency " \
                                  "FROM  list_cds_positions_by_strat(%s)",
                                  dbengine("dawndb"), params=(d.date(),))
    instruments = positions.security_desc.str.split(expand=True)[[1, 3, 4]]
    instruments.columns = ['index_type', 'series', 'tenor']
    instruments.series = instruments.series.str.extract("S(\d+)")
    instruments.index_type[instruments.index_type == "EUR"] = "EU"
    positions = positions.join(instruments)
    del positions['security_desc']
    positions = positions.set_index(['index_type', 'series', 'tenor'])
    df = positions.join(df)
    d = {}
    def f(g):
        g.Collateral = g.Collateral * g.notional / g.notional.sum()
        return g
    df = (df.groupby(level=['index_type', 'series', 'tenor']).
          apply(f))
    df = df.groupby(['folder']).agg({'Collateral': 'sum', 'currency': 'first'})
    df = df.reset_index('folder')
    df = df.rename(columns=
                   {'folder': 'Strategy',
                    'currency': 'Currency',
                    'Collateral': 'Amount'})
    df.Strategy = df.Strategy.map({'HEDGE_MBS': 'MBSCDSCSH',
                                   'SER_ITRXCURVE': 'SER_ITRXCVCSH',
                                   'SER_IGCURVE': 'SER_IGCVECSH'})
    return df

def ms_collateral(d):
    df = pd.read_excel(DAILY_DIR / "MS_reports" / f"{d:%Y%m%d}.xls" )
    d = {'TRCDX': 'IGTCDSCSH',
         'ABINT': 'MBSCDSCSH'}
    r = []
    acc = 0
    for k, v in d.items():
        amount = df.loc[df.trade_book == k, "collat_req_in_agr_ccy"].sum()
        r.append((v, amount, "USD"))
        acc += amount
    r.append(["M_CSH_CASH", -6_810_000 - acc, "USD"])
    return pd.DataFrame.from_records(r, columns=['Strategy', 'Amount', 'Currency'])

def send_email(account, df_ms, df_sg):
    pd.set_option('display.float_format', '{:.2f}'.format)
    content = HTMLBody('<html><body>' \
                       '<h3>At Morgan Stanley:</h3>' \
                       '{}' \
                       '<h3>At Societe Generale:</h3>'
                       '{}' \
                       '</body><html>'.format(df_ms.to_html(index=False), df_sg.to_html(index=False)))
    m = Message(
        account=account,
        folder=account.sent,
        subject='IAM booking',
        body=content,
        to_recipients=[Mailbox(email_address='serenitas.otc@sscinc.com')],
        cc_recipients=['nyops@lmcg.com']
    )
    m.send_and_save()

if __name__ == "__main__":
    download_emails()
    d = (pd.Timestamp.today() - BDay()).normalize()
    download_files(d)
    df_ms = ms_collateral(d - BDay())
    df_sg = sg_collateral(d)
    account = get_account('ghorel@lmcg.com')
    send_email(account, df_ms, df_sg)