from serenitas.utils.db import dbconn
from ops.funds import Fund
import datetime
from serenitas.analytics.dates import prev_business_day
from collateral.common import CASH_STRATEGY_MAPPING, STRATEGY_CASH_MAPPING
from serenitas.utils.misc import rename_keys


def generate_csv(date, conn):
    bowdst = Fund["BOWDST"]()
    with conn.cursor() as c, conn.cursor() as d:
        c.execute(
            "SELECT vr.*, accounts2.custodian, accounts2.cp_code, pfm.folder AS dirty_strat FROM (SELECT sum(endqty) AS endqty, port, strat, custacctname, invid FROM valuation_reports v WHERE periodenddate =%s AND fund='BOWDST' AND invid IN ('USD', 'EUR') AND port NOT IN ('GFS_HELPER_BUSINESS_UNIT', 'CASH') GROUP BY (port, strat,custacctname, invid) HAVING sum(endqty) !=0) vr LEFT JOIN accounts2 ON custacctname=cash_account LEFT JOIN portfolio_folder_mapping pfm ON vr.strat::text=pfm.clean_folder where strat is not NULL;",
            (date,),
        )
        for row in c:
            if row.strat not in CASH_STRATEGY_MAPPING:
                d.execute(
                    "INSERT INTO strat_cash_realloc (portfolio, folder, trade_date, amount, currency, fund, cash_account) VALUES (%s, %s, %s, %s, %s, %s, %s) RETURNING dealid",
                    (
                        row.port,
                        row.strat,
                        date,
                        row.endqty,
                        row.invid,
                        "BOWDST",
                        row.custacctname,
                    ),
                )
                (dealid,) = d.fetchone()
                obj = row._asdict()
                obj["dirty_strat"] = row.dirty_strat if row.dirty_strat else row.strat
                rename_keys(
                    obj,
                    {
                        "invid": "Currency",
                        "custacctname": "Cash Account",
                        "custodian": "Custodian",
                        "cp_code": "Counterparty",
                        "dirty_strat": "Folder",
                    },
                )
                data = {
                    "Deal Type": "CashFlowDeal",
                    "Deal Id": dealid,
                    "Action": "NEW",
                    "Client": "HEDGEMARK",
                    "Fund": "BOS_PAT_BOWDOIN",
                    "State": "Valid",
                    "Trade Date": date,
                    "Settlement Date": date,
                    "Transaction Type": "Transfer",
                    "Instrument Type": "Cashflow",
                    "Amount": -obj["endqty"],
                }
                obj.update(data)
                offset = obj.copy()
                # create second leg
                offset["Deal Id"] = obj["Deal Id"] + "_O"
                offset["Amount"] = -obj["Amount"]
                offset["Folder"] = STRATEGY_CASH_MAPPING[obj["Folder"]]
                bowdst.staging_queue.extend([obj, offset])
    conn.commit()
    if bowdst.staging_queue:
        buf, dest = bowdst.build_buffer("wire")
        bowdst.upload(buf, dest.name)


if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser()
    parser.add_argument(
        "workdate",
        nargs="?",
        type=datetime.date.fromisoformat,
        default=prev_business_day(datetime.date.today()),
        help="working date",
    )
    args = parser.parse_args()

    conn = dbconn("dawndb")
    generate_csv(args.workdate, conn)