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:
        c.execute("SELECT headers from csv_templates where template_name='bbh_bonds'")
        (headers,) = c.fetchone()
        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' and faceamount is not null and faceamount >0;"
        c.execute(
            sql_query, (datetime.date(2022, 4, 5),)
        )  # We don't want to upload trades before this date
        for row in c:
            obj = row._asdict()
            if obj["current_face"] == 0:
                raise Exception(f"Current face missing {obj['dealid']}")
            rename_keys(
                obj,
                {
                    "dealid": "Client Reference Number",
                    "identifier": "Security ID",
                    "accrued_payment": "Interest Amount",
                    "dtc_number": "Trading Broker Type/ID",
                    "principal_payment": "Principal Amount",
                    "faceamount": "Unit / Original Face Amount",
                    "current_face": "Current Face/Amortize Value",
                    "price": "Unit Price Amount",
                    "net_amount": "Net Amount",
                },
            )
            obj["Trade Date"] = obj["trade_date"].strftime("%m/%d/%Y")
            obj["Settlement Date"] = obj["settle_date"].strftime("%m/%d/%Y")
            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 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(headers)
                csvwriter.writerow([obj.get(header, None) for header in 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)