import pandas as pd
import socket
from . import DAILY_DIR
from .common import compare_notionals
from paramiko import Transport, SFTPClient
from sqlalchemy.exc import IntegrityError
from ssh2.session import Session
from ssh2.sftp import LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR, LIBSSH2_SFTP_S_IFREG


def get_wells_sftp_client():
    transport = Transport(("axst.wellsfargo.com", 10022))
    transport.connect(username="LMCHsWC6EP", password="HI2s2h19+")
    return SFTPClient.from_transport(transport)


def get_wells_sftp_client2():
    sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
    sock.connect(("axst.wellsfargo.com", 10022))
    session = Session()
    session.handshake(sock)
    session.userauth_password("LMCHsWC6EP", "HI2s2h19+")
    sftp = session.sftp_init()
    return sftp


def download_files2(d=None):
    DATA_DIR = DAILY_DIR / "Wells_reports"
    sftp = get_wells_sftp_client()
    base_dir = "/RECEIVE/339425_DATO2"
    for f in sftp.listdir(base_dir):
        if not (DATA_DIR / f).exists():
            sftp.get(f"{base_dir}/{f}", localpath=DATA_DIR / f)


def download_files(d=None):
    DATA_DIR = DAILY_DIR / "Wells_reports"
    sftp = get_wells_sftp_client2()
    files = []
    with sftp.opendir("/RECEIVE/339425_DATO2") as fh:
        for size, buf, attrs in fh.readdir():
            if attrs.permissions & LIBSSH2_SFTP_S_IFREG:
                files.append(buf.decode())
    for f in files:
        local_file = DATA_DIR / f
        if not local_file.exists():
            with sftp.open(
                f"/RECEIVE/339425_DATO2/{f}", LIBSSH2_FXF_READ, LIBSSH2_SFTP_S_IRUSR
            ) as remote_handle, local_file.open("wb") as local_handle:
                for size, data in remote_handle:
                    local_handle.write(data)


def collateral(d, positions, engine):
    account = "A5882186"
    file_name = (
        DAILY_DIR
        / "Wells_reports"
        / f"OTC_CDS_Position_Activity_{account}_{d:%m%d%Y}.csv"
    )
    try:
        df = pd.read_csv(
            file_name,
            usecols=[
                "TENOR",
                "MARKET_VALUE_NPV",
                "PAIR_CLIP",
                "BUY_SELL",
                "NOTIONAL",
                "MATURITY_DATE",
                "TRADE_PRICE",
            ],
            parse_dates=["MATURITY_DATE"],
            index_col=["PAIR_CLIP", "MATURITY_DATE"],
        )
    except ValueError:
        # backpopulated files have a different format...
        df = pd.read_csv(
            file_name,
            usecols=[
                "Tenor",
                "NPV",
                "Reference_Entity_ID",
                "Fixed_Rate_Notional_Buy",
                "Amount",
                "Scheduled_Termination_Date",
            ],
            parse_dates=["Scheduled_Termination_Date"],
            index_col=["Reference_Entity_ID", "Scheduled_Termination_Date"],
        )
        df = df.rename(
            columns={"Tenor": "TENOR", "NPV": "MARKET_VALUE_NPV", "Amount": "NOTIONAL"}
        )
        df["BUY_SELL"] = 1
        df.loc[df.Fixed_Rate_Notional_Buy.isnull(), "BUY_SELL"] = 2
        del df["Fixed_Rate_Notional_Buy"]
    df = df[df.TRADE_PRICE != 0.0]
    del df["TRADE_PRICE"]
    df["NOTIONAL"] = df.NOTIONAL.where(df.BUY_SELL == 1, -df.NOTIONAL).astype("float")
    df["DIRTYUPFRONT"] = df.MARKET_VALUE_NPV / df.NOTIONAL
    df.index.names = ["security_id", "maturity"]
    compare_notionals(df, positions, "Wells")
    positions = positions.join(df, how="left")
    positions["amount"] = positions["notional"] * positions["DIRTYUPFRONT"]
    positions.folder = positions.folder.map(
        {
            "HEDGE_MBS": "MBSCDSCSH",
            "SER_ITRXCURVE": "SER_ITRXCVCSH",
            "SER_IGCURVE": "SER_IGCVECSH",
            "HYOPTDEL": "COCSH",
            "IGOPTDEL": "COCSH",
            "IGINX": "TCSH",
            "HYINX": "TCSH",
        }
    )
    df = (
        positions.groupby("folder")
        .agg({"amount": "sum", "currency": "first"})
        .reset_index("folder")
    )
    df.columns = ["Strategy", "Amount", "Currency"]
    df_margin = pd.read_csv(
        DAILY_DIR
        / "Wells_reports"
        / f"OTC_Moneyline_Activity_{account}_{d:%m%d%Y}.csv",
        usecols=["CURRENCY_NAME", "CURRENT_IM", "VALUE_DATE"],
        parse_dates=["VALUE_DATE"],
        index_col=["CURRENCY_NAME"],
    )
    try:
        engine.execute(
            "INSERT INTO fcm_im " "VALUES(%s, 'WFNSCLMFCM', 'USD', %s)",
            df_margin.loc["ZZZZZ", ["VALUE_DATE", "CURRENT_IM"]].tolist(),
        )
    except IntegrityError:
        pass
    df["date"] = d
    return df.set_index("Strategy")