import pandas as pd
from serenitas.utils.env import DAILY_DIR
from serenitas.utils.db import dawn_engine, dbconn
from serenitas.utils.remote import SftpClient
import datetime
from serenitas.analytics.dates import prev_business_day


def load_citco_report(fdir):
    df = pd.read_csv(fdir)
    df["row"] = df.index
    df.columns = df.columns.str.lower()
    df.columns = df.columns.str.replace(" ", "_")
    df["period_end_date"] = pd.to_datetime(df["period_end_date"], format="%Y%m%d")
    return df


if __name__ == "__main__":
    date = datetime.date.today()
    reports_dir = DAILY_DIR / str(date) / "Reports"
    sftp = SftpClient.from_creds("citco")
    for f in sftp.client.listdir("/outgoing"):
        if "SPOS4X" in f:
            sftp.client.get(
                f"/outgoing/{f}", localpath=reports_dir / f"Valuation_Report_ISOSEL.csv"
            )
    df = load_citco_report(reports_dir / f"Valuation_Report_ISOSEL.csv")
    conn = dbconn("dawndb")
    with conn.cursor() as c:
        c.execute(
            "DELETE from citco_reports where period_end_date =%s",
            (prev_business_day(date),),
        )
    conn.commit()
    df.to_sql("citco_reports", dawn_engine, if_exists="append", index=False)