from serenitas.utils.db import dbconn
import datetime
import csv
from io import StringIO
from pathlib import Path
from process_queue import rename_keys


def process_upload(trades, asset_type):
    buf = StringIO()
    csvwriter = csv.writer(buf)
    csvwriter.writerow(HEADERS[asset_type])
    csvwriter.writerows(build_line(trade, asset_type) for trade in trades)
    buf = buf.getvalue().encode()
    dest = Path(f"/home/serenitas/flint/{asset_type}.csv")
    dest.write_bytes(buf)


def build_line(obj, asset_type):
    return [obj.get(h, None) for h in HEADERS[asset_type]]


# variables

date = datetime.date(2021, 11, 9)


dawndb = dbconn("dawndb")


HEADERS = {
    "bond": [
        "AccountNumber",
        "COB Date",
        "Prime Broker",
        "SecurityType",
        "CUSIP",
        "ISIN",
        "SEDOL",
        "SecurityDescription",
        "Position",
        "MarketPrice",
        "Currency",
        "Base Market Value",
        "Local Market Value",
        "Fx Rate",
    ],
    "future": [
        "AccountNumber",
        "COB Date",
        "Prime Broker",
        "SecurityType",
        "BBGTicker",
        "RIC",
        "UnderlyingSecurity",
        "SecurityDescription",
        "Currency",
        "Quantity",
        "OpenTradeEquity",
        "ClosingPrice",
        "MaturityDate",
        "Unrealised P&L in USD",
        "Local Market Value",
        "Fx Rate",
    ],
    "otc": [
        "Client Name",
        "Fund Name",
        "Counterparty",
        "Product Type",
        "Unique Deal ID",
        "TransactionIndicator (Buy/Sell)",
        "PutCall Indicator (Call/Put)",
        "CapFloorIndicator",
        "CurrencyPair",
        "DealCurrencyA",
        "DealCurrencyB",
        "NotionalA",
        "NotionalB",
        "OriginalPrice",
        "Strike",
        "FixedRate",
        "Quantity",
        "Start Date",
        "Effective Date",
        "Maturity Date",
        "Underlying Maturity",
        "RecPayFixed",
        "Underlying (ISIN / CUSP / RED CODES)",
        "Underlying Desc",
        "Exercise Type",
        "MTM Currency",
        "MTM Valuation",
        "COB Date",
        "Clearing House Name",
    ],
}


with dawndb.cursor() as c:
    c.execute("SELECT * FROM risk_positions(%s, null, 'BOWDST') ", (date,))
    trades = []
    for row in c:
        obj = row._asdict()
        rename_keys(
            obj,
            {
                "identifier": "CUSIP",
                "description": "SecurityDescription",
                "notional": "Position",
                "price": "MarketPrice",
                "local_market_value": "Local Market Value",
                "usd_market_value": "Base Market Value",
            },
        )
        try:
            obj["Fx Rate"] = obj["Local Market Value"] / obj["Base Market Value"]
        except ZeroDivisionError:
            obj["Fx Rate"] = 1
        obj["AccountNumber"] = "TELHEEACPB"
        obj["Prime Broker"] = "TEST"
        obj["COB Date"] = date
        trades.append(obj)
        process_upload(trades, "bond")

    c.execute(
        "SELECT trb.trade_id, trb.serenitas_clean_nav + trb.serenitas_accrued as mtm, trb.notional as active_notional, cds.* FROM tranche_risk_bowdst trb left join cds on trade_id=id WHERE date=%s",
        (date,),
    )
    otc_trades = []
    for row in c:
        obj = row._asdict()
        obj["Client Name"] = "HEDGEMARK"
        obj["Fund Name"] = "BOS_PAT_BOWDOIN"
        obj["Product Type"] = "Credit Index Tranche"
        obj["TransactionIndicator (Buy/Sell)"] = (
            "B" if obj["protection"] == "Buyer" else "S"
        )
        obj["MTM Currency"] = "USD"
        obj["COB Date"] = date
        obj["Clearing House Name"] = "Bilateral"
        rename_keys(
            obj,
            {
                "dealid": "Unique Deal ID",
                "cp_code": "Counterparty",
                "currency": "DealCurrencyA",
                "active_notional": "NotionalA",
                "fixed_rate": "FixedRate",
                "trade_date": "Start Date",
                "effective_date": "EffectiveDate",
                "maturity": "Maturity Date",
                "security_id": "Underlying (ISIN / CUSP / RED CODES)",
                "security_desc": "Underlying Desc",
                "mtm": "MTM Valuation",
            },
        )
        otc_trades.append(obj)
    c.execute(
        "SELECT abs(spr.notional) AS active_notional, spr.serenitas_nav, swaptions.*, index_version_markit.annexdate FROM list_swaption_positions_and_risks(%s, 'BOWDST') spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
        (date,),
    )
    for row in c:
        obj = row._asdict()
        obj["Client Name"] = "HEDGEMARK"
        obj["Fund Name"] = "BOS_PAT_BOWDOIN"
        obj["Product Type"] = "CD Swaption"
        obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S"
        obj["PutCall Indicator (Call/Put)"] = (
            "P" if obj["option_type"] == "PAYER" else "C"
        )
        obj["Exercise Type"] = "European"
        obj["MTM Currency"] = "USD"
        obj["COB Date"] = date
        obj["Clearing House Name"] = "Bilateral"
        rename_keys(
            obj,
            {
                "dealid": "Unique Deal ID",
                "cp_code": "Counterparty",
                "currency": "DealCurrencyA",
                "active_notional": "NotionalA",
                "fixed_rate": "FixedRate",
                "strike": "Strike",
                "annexdate": "EffectiveDate",
                "trade_date": "Start Date",
                "maturity": "Maturity Date",
                "expiration_date": "Underlying Maturity",
                "security_id": "Underlying (ISIN / CUSP / RED CODES)",
                "security_desc": "Underlying Desc",
                "serenitas_nav": "MTM Valuation",
            },
        )
        otc_trades.append(obj)

    c.execute(
        "SELECT abs(spr.notional) AS active_notional, spr.nav, swaptions.*, index_version_markit.effectivedate FROM list_ir_swaption_positions(%s, 'BOWDST') spr LEFT JOIN swaptions  ON deal_id=dealid LEFT JOIN index_version_markit ON swaptions.security_id=redindexcode;",
        (date,),
    )
    for row in c:
        obj = row._asdict()
        obj["Client Name"] = "HEDGEMARK"
        obj["Fund Name"] = "BOS_PAT_BOWDOIN"
        obj["Product Type"] = "Swaption"
        obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["buysell"] else "S"
        obj["PutCall Indicator (Call/Put)"] = (
            "P" if obj["option_type"] == "PAYER" else "C"
        )
        obj["Exercise Type"] = "European"
        obj["MTM Currency"] = "USD"
        obj["COB Date"] = date
        obj["Clearing House Name"] = "Bilateral"
        rename_keys(
            obj,
            {
                "dealid": "Unique Deal ID",
                "cp_code": "Counterparty",
                "currency": "DealCurrencyA",
                "active_notional": "NotionalA",
                "fixed_rate": "FixedRate",
                "strike": "Strike",
                "effectivedate": "Effective Date",
                "trade_date": "Start Date",
                "maturity": "Maturity Date",
                "expiration_date": "Underlying Maturity",
                "security_id": "Underlying (ISIN / CUSP / RED CODES)",
                "security_desc": "Underlying Desc",
                "nav": "MTM Valuation",
            },
        )
        otc_trades.append(obj)

    c.execute(
        "SELECT cds.*, ivm.effectivedate FROM list_cds_marks(%s, null, 'BOWDST') cds LEFT JOIN index_version_markit ivm ON security_id=redindexcode;",
        (date,),
    )
    for row in c:
        obj = row._asdict()
        obj["Client Name"] = "HEDGEMARK"
        obj["Fund Name"] = "BOS_PAT_BOWDOIN"
        obj["Counterparty"] = "GS"
        obj["Product Type"] = "Credit Index"
        obj["Unique Deal ID"] = obj["security_id"]
        obj["TransactionIndicator (Buy/Sell)"] = "B" if obj["notional"] > 0 else "S"
        obj["DealCurrencyA"] = "EUR" if obj["index"] in ("EU", "XO") else "USD"
        obj["NotionalA"] = abs(obj["notional"])
        obj["Start Date"] = date
        obj["MTM Currency"] = "USD"
        obj["MTM Valuation"] = obj["clean_nav"] + obj["accrued"]
        obj["COB Date"] = date
        obj["Clearing House Name"] = "ICE"
        rename_keys(
            obj,
            {
                "coupon": "FixedRate",
                "effectivedate": "Effective Date",
                "maturity": "Maturity Date",
                "security_id": "Underlying (ISIN / CUSP / RED CODES)",
                "security_desc": "Underlying Desc",
            },
        )
        otc_trades.append(obj)
    process_upload(otc_trades, "otc")