from io import BytesIO
from serenitas.utils.env import DAILY_DIR
import datetime
from serenitas.ops.citco import GIL, GTL
from serenitas.utils.remote import Client
from serenitas.utils.db import dbconn
from serenitas.analytics.dates import prev_business_day, next_business_day
from report_ops.sma import build_position_file
from report_ops.utils import SettlementMonitor

settlement_sum_query = "SELECT currency, sum(payment_amount) as payment_amount FROM payment_settlements ps2 WHERE fund=%s AND asset_class in ('BOND', 'SPOT') AND settle_date BETWEEN %s AND %s group by currency;"
_account_fund = {"ISOS01": "ISOSEL"}


def concat_csv(file_type, date):
    match file_type:
        case "trade":
            file_tag = "innocap_serenitas_trades"
        case "instrument":
            file_tag = "i.innocap_serenitas"
    fname = f"Innocap_ISOSEL_{file_type}_{date:%Y-%m-%d}.csv"
    buf = BytesIO()
    buf.write((",".join(GTL if file_type == "trade" else GIL) + "\n").encode())
    for f in (DAILY_DIR / str(date)).iterdir():
        if f.is_file() and f.name.startswith(file_tag):
            with f.open("rb") as fh:
                next(fh)  # Don't concat headers
                buf.write(fh.read())
    return buf.getvalue(), fname


def upload_citco_files(date, upload):
    for file_type in ("trade", "instrument"):
        buf, fname = concat_csv(file_type, date)
        dest = DAILY_DIR / str(date) / fname
        dest.write_bytes(buf)
        if upload:
            innocap_sftp = Client.from_creds("innocap", folder="Innocap")
            innocap_sftp.put(buf, fname)


def upload_position_files(date, fund, upload):
    buf, dest = build_position_file(
        date,
        fund,
    )
    if upload:
        client = Client.from_creds("innocap", folder="Innocap")
        client.put(buf, dest.name)
        client = Client.from_creds("citco")
        client.put(buf, dest.name)


def get_cash_balance(date, account, conn):
    with conn.cursor() as c:
        sql_str = "SELECT balance, currency_code from cash_balances WHERE account_number=%s AND date=%s"
        c.execute(sql_str, (account, date))
        return {row.currency_code: row.balance for row in c}


def monitor_cash_balances(date, account, conn, upload):
    cob = prev_business_day(date)
    fund = _account_fund[account]
    if cash_balances := get_cash_balance(cob, account, conn):
        for projection_date in (date, next_business_day(date)):
            with conn.cursor() as c:
                c.execute(settlement_sum_query, (fund, date, projection_date))
                for row in c:
                    projection_balance = (
                        cash_balances[row.currency] + row.payment_amount
                    )
                    if projection_balance < 0:
                        SettlementMonitor.stage(
                            {
                                "date": projection_date,
                                "account": account,
                                "currency": row.currency,
                                "projected_balance": projection_balance,
                            }
                        )
        SettlementMonitor.email(fund)
        SettlementMonitor._insert_queue.clear()

    else:
        raise ValueError(f"No cash balances parsed for account {account}: {cob}")


if __name__ == "__main__":
    import argparse

    parser = argparse.ArgumentParser()
    parser.add_argument(
        "workdate",
        nargs="?",
        type=datetime.date.fromisoformat,
        default=datetime.date.today(),
        help="file transfer date",
    )
    parser.add_argument(
        "--no-upload",
        "-n",
        action="store_true",
        default=False,
        help="uploads to citco and innocap",
    )
    args = parser.parse_args()
    cob = prev_business_day(args.workdate)
    conn = dbconn("dawndb")

    upload_citco_files(cob, not args.no_upload)
    upload_position_files(cob, "ISOSEL", not args.no_upload)
    monitor_cash_balances(args.workdate, "ISOS01", conn, not args.no_upload)