from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient
from zoneinfo import ZoneInfo
import datetime
import csv
from trade_dataclasses import CDSDeal, BondDeal
from decimal import Decimal
from serenitas.utils.db import dbconn
from lru import LRU
from stat import S_ISREG
import pandas as pd
from sqlalchemy.exc import SQLAlchemyError
import time

_funds = {"SERENITAS_CGMF": "SERCGMAST", "BOWDOINST": "BOWDST"}
_fcms = {"Bank of America, N.A.": "BAML", "Goldman Sachs": "GS"}
_cdx_cp = {
    "MSDU": "MSCSNY",
    "GSMX": "GOLDNY",
    "JPGP": "JPCBNY",
    "JFF": "JEFF",
    "BMLE": "BAMSNY",
    "BARX": "BARCNY",
    "CSDA": "CSFBBO",
    "EBNP": "BNPBNY",
    "WFCD": "WELFEI",
    "BSEF": "BSEONY",
    "JPOS": "JPCBNY",
    "CGCI": "CITINY",
}
_bond_cp = {
    "CG": "CITINY",
    "WFBS": "WELFEI",
    "MZZ": "MIZUNY",
    "BABS": "BAML",
    "PTRU": "PERFCH",
    "BARC": "BARCNY",
    "MS": "MORGNY",
    "BA": "BAML",
    "FB": "CSUINY",
    "INTC": "STONEX",
    "SOCG": "SGSANY",
    "NOM": "NOMINY",
    "JP": "JPCBNY",
    "BTIG": "BTIG",
}


def get_indic_data(conn, redcode, tenor):
    sql_str = (
        "SELECT maturity, coupon "
        "FROM index_desc "
        "WHERE tenor=%s AND redindexcode=%s "
    )
    with conn.cursor() as c:
        c.execute(sql_str, (redcode, tenor))
        return c.fetchone()


def cdx_booking_process(path, conn):
    reader = csv.DictReader(path)
    for line in reader:
        tenor = line["Security"].rsplit(" ", 1)[-1].lower() + "r"
        maturity, coupon = get_indic_data(conn, tenor, line["Red Code"])
        trade = CDSDeal(
            fund=_funds[line["Account"]],
            folder="*",
            portfolio="UNALLOCATED",
            security_id=line["Red Code"],
            security_desc=line["Security"].removesuffix(" PRC"),
            traded_level=Decimal(line["Price (Dec)"]),
            notional=line["Quantity"],
            fixed_rate=coupon * 0.01,
            trade_date=datetime.datetime.strptime(line["Trade Dt"], "%m/%d/%Y").date(),
            maturity=maturity,
            currency=line["Curncy"],
            protection="Buyer" if line["Side"] == "B" else "Seller",
            upfront=line["Principal"],
            cp_code=_cdx_cp[line["Brkr"]],
            account_code=_fcms[line["Client FCM"]],
        )
        trade.stage()
    CDSDeal.commit()


def bond_booking_process(path, fname):
    reader = csv.DictReader(path)
    for line in reader:
        line["bbg_ticket_id"] = fname
        trade = BondDeal
        trade.stage()
        try:
            BondDeal.insert_bbg_line(line)
        except Exception as e:
            pass
            BondDeal._insert_queue.clear()
            print("error")
        else:
            _cache[fname] = None
        finally:
            BondDeal.commit()


def get_bbg_id(name):
    return name.split("_", 1)[1]


if __name__ == "__main__":
    from serenitas.utils.db import serenitas_pool, dawn_engine

    conn = serenitas_pool.getconn()
    _cache = LRU(128)
    while True:
        d = datetime.date.today()
        sftp = SftpClient.from_creds("bbg")
        filters = [lambda f: S_ISREG(f.st_mode)]
        for f in filter(
            lambda f: all(filt(f) for filt in filters), sftp.client.listdir_iter("/")
        ):
            if "BOND" in f.filename:
                bbg_id = get_bbg_id(f.filename)
                if bbg_id not in BondDeal._cache:
                    bond_booking_process(sftp.client.open(f.filename), bbg_id)
        time.sleep(60)