from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient
import datetime
import pytz
from stat import S_ISREG
import csv
from process_queue import rename_keys
from serenitas.utils.db import dbconn

fund_dictionary = {"SERENITAS_CGMF": "SERCGMAST", "BOWDOINST": "BOWDST"}
fcm_dictionary = {"Bank of America, N.A.": "BAML", "Goldman Sachs": "GS"}


def download_files(date):
    downloaded_files = []
    sftp = SftpClient.from_creds("bbg")
    dst = DAILY_DIR / f"{date:%Y-%m-%d}" / "bbg_tickets"
    if not dst.exists():
        dst.mkdir()
    est = pytz.timezone("US/Eastern")
    src = ""
    for f in sftp.client.listdir_iter():
        if S_ISREG(f.st_mode):
            local_file = dst / f.filename
            modification_time = datetime.datetime.fromtimestamp(
                f.st_mtime, tz=datetime.timezone.utc
            ).astimezone(est)
            if not local_file.exists() and (modification_time.date() == date):
                sftp.client.get(f"{src}/{f.filename}", localpath=local_file)
                downloaded_files.append(local_file)
    return downloaded_files


def get_bbg_data(bbg_id, trade_date):
    try:
        _, indextype, _, series, tenor = bbg_id.split()
    except ValueError:
        return "not a valid bloomberg description", 400
    indextype = indextype[:2]
    tenor = tenor[:-1] + "yr"
    series = int(series[1:])
    sql_str = (
        "SELECT redindexcode, maturity, coupon "
        "FROM index_desc "
        "WHERE index=%s and series=%s and tenor=%s "
        "      and lastdate >=%s ORDER BY version"
    )
    db = get_db()
    with db.cursor() as c:
        c.execute(sql_str, (indextype, series, tenor, trade_date))
        redcode, maturity, coupon = c.fetchone()
    return str(maturity), redcode, coupon


def cdx_booking_process(path):
    reader = csv.DictReader(open(path))
    for csvdict in reader:
        rename_keys(
            csv_dict,
            {
                "Curncy": "currency",
                "Quantity": "notional",
            },
        )
        csv_dict["security_desc"] = csv_dict["security_desc"].replace(" PRC", "")
        csv_dict["traded_level"] = float(csv_dict["Price (Dec)"])
        csv_dict["notional"] = float(csv_dict["Quantity"])
        csv_dict["trade_date"] = datetime.datetime.strptime(
            csv_dict["Trade Dt"], "%Y-%m-%d"
        )
        csv_dict["upfront_settle_date"] = datetime.datetime.strptime(
            csv_dict["SetDt"], "%Y-%m-%d"
        )
        csv_dict["protection"] = "Buyer" if csv_dict["Side"] == "B" else "Seller"
        csv_dict["account_code"] = fcm_dictionary[csv_dict["Client FCM"]]
        csv_dict["fund"] = fund_dictionary[csv_dict["Account"]]
        csv_dict["action"] = "NEW"
        csv_dict["folder"] = None
        csv_dict["cp_code"] = None
        csv_dict["payment_rolldate"] = "Following"
        csv_dict["day_count"] = "ACT/360"
        csv_dict["frequency"] = 4
        csv_dict["swap_type"] = "CD_INDEX"
        csv_dict["portfolio"] = None
        (
            csv_dict["maturity"],
            csv_dict["security_id"],
            csv_dict["fixed_rate"],
        ) = get_bbg_data(csv_dict["security_desc"], csv_dict["trade_date"])
        csv_dict["effective_date"] = datetime.date(2021, 12, 20)


def book_trades(date):
    downloaded_files = download_files(date)
    if downloaded_files:
        for f in downloaded_files:
            if "CDX" in f.name:
                cdx_booking_process(f)


if __name__ == "__main__":
    book_trades(datetime.date(2022, 2, 4))