from headers import BBH_BOND_HEADERS
import csv
from serenitas.utils.db import dbconn, dawn_engine
import datetime
from process_queue import rename_keys
import pandas as pd
from sqlalchemy.exc import IntegrityError
from io import StringIO
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.remote import SftpClient

if __name__ == "__main__":
    conn = dbconn("dawndb")
    with conn.cursor() as c:
        sql_query = "SELECT bond_trades.*, counterparties.dtc_number  FROM bond_trades LEFT JOIN counterparties ON cp_code=code WHERE cash_counterparty AND trade_date >= '2022-04-05' AND fund='BRINKER';"
        c.execute(sql_query, (datetime.date(2022, 4, 5),))
        for row in c:
            obj = row._asdict()
            rename_keys(
                obj,
                {
                    "dealid": "Client Reference Number",
                    "identifier": "Security ID",
                    "accrued_payment": "Interest Amount",
                    "dtc_number": "Trading Broker Type/ID",
                    "principal_payment": "Principal Amount",
                    "trade_date": "Trade Date",
                    "settle_date": "Settlement Date",
                    "faceamount": "Unit / Original Face Amount",
                    "current_face": "Current Face/Amortize Value",
                    "price": "Unit Price Amount",
                    "net_amount": "Net Amount",
                },
            )
            obj["Place of Settlement/Country"] = "DTCYUS33"
            obj["Transaction Type"] = "RVP" if obj["buysell"] else "DVP"
            obj["Function of Instruction"] = "NEWM"
            obj["Account Number"] = "4023461"
            obj["Currency"] = "USD"
            obj["Clearing Broker ID / Type"] = obj["Trading Broker Type/ID"]
            obj["Other Fees Amount"] = 0
            obj["Commission Amount"] = 0
            obj["SEC Fees Amount"] = 0
            new_obj = {"tradeid": obj["tradeid"]}
            for header in BBH_BOND_HEADERS:
                new_obj[header] = obj.get(header, None)
            df = pd.DataFrame(
                new_obj,
                index=[
                    "i",
                ],
            )
            try:
                df.to_sql(
                    "bbh_bond_upload", dawn_engine, index=False, if_exists="append"
                )
            except IntegrityError:
                conn.rollback()
            else:
                buf = StringIO()
                csvwriter = csv.writer(buf)
                csvwriter.writerow(BBH_BOND_HEADERS)
                csvwriter.writerow(
                    [obj.get(header, None) for header in BBH_BOND_HEADERS]
                )
                buf = buf.getvalue().encode()
                fname = f'LMCG_BBH_TRADES_P.{obj["Client Reference Number"].replace("_", "")}.csv'
                dest = DAILY_DIR / str(datetime.date.today()) / fname
                sftp = SftpClient.from_creds("bbh")
                sftp.put(buf, fname)
                dest.write_bytes(buf)