from serenitas.utils.remote import SftpClient
from typing import Callable, List
import pandas as pd
from serenitas.utils.db import dawn_engine, dbconn
import datetime
import re
from serenitas.analytics.dates import prev_business_day, next_business_day
from .misc import dt_from_citco
from functools import partial
from typing import ClassVar


def load_citco_report(fh, kd, date_cols):
    df = pd.read_csv(fh, parse_dates=date_cols, infer_datetime_format=True)
    df["row"] = df.index
    df.columns = df.columns.str.lower()
    df.columns = df.columns.str.replace(" ", "_")
    df["period_end_date"] = kd.date()
    df["knowledge_date"] = next_business_day(kd)
    return df


class Report:
    table: str
    _sftp = SftpClient.from_creds("citco", folder="outgoing")
    _conn: dbconn = dbconn("dawndb")
    date_cols: List[str] = []
    dtkey_fun: ClassVar

    def __init_subclass__(cls, table, fname, date_cols, dtkey):
        cls.table = table
        cls.fname = fname
        cls.date_cols = date_cols
        cls.dtkey_fun = partial(dt_from_citco, file_tag=cls.fname, dt_format=dtkey)

    def __init__(self, date):
        self.date = date

    @classmethod
    def get_newest_report(cls, date):
        p = max(
            [
                f
                for f in cls._sftp.client.listdir()
                if (cls.fname in f) and (cls.dtkey_fun(f).date() == date)
            ],
            key=cls.dtkey_fun,
            default=None,
        )
        if not p:
            raise ValueError(f"No reports for {cls.fund} on {date}")
        return p

    @classmethod
    def to_df(cls, fname):
        kd = cls.dtkey_fun(fname)
        with cls._sftp.client.open(fname) as fh:
            return load_citco_report(fh, kd, cls.date_cols)

    def to_db(self):
        p = self.get_newest_report(self.date)
        df = self.to_df(p)
        with self._conn.cursor() as c:
            c.execute(
                f"DELETE FROM {self.table} WHERE period_end_date= %s",
                (self.date,),
            )
        self._conn.commit()
        if "strategy" in df.columns:
            df["strategy"] = df["strategy"].str.replace("/M_|/SER_", "/", regex=True)
        df.to_sql(self.table, dawn_engine, if_exists="append", index=False)


class AccruedReport(
    Report,
    table="isosel_accrued",
    fname="100502500_INNOCAP_ISOSEL_",
    date_cols=[
        "Init Date",
        "Init Settle Date",
        "Liqd Date",
        "Liqd Settle Date",
        "Bond Maturity",
        "Orig Date",
        "Start Date",
        "End Date",
    ],
    dtkey="%Y%m%d%H%M%S",
):
    pass


class AllReport(
    Report,
    table="citco_reports",
    fname="SPOS4X_INNOCAP_ISOSEL_D_IM.",
    date_cols=["Maturity Date"],
    dtkey="%Y%m%d.%H%M%S",
):
    pass