import datetime
import re
from typing import ClassVar
from dataclasses import dataclass
import pandas as pd
from pandas.errors import EmptyDataError

from serenitas.ops.dataclass_mapping import Fund
from serenitas.analytics.dates import prev_business_day
from serenitas.analytics.exceptions import MissingDataError
from serenitas.utils.env import DAILY_DIR

from .misc import get_dir, Custodian
from .base import Report


@dataclass
class WireReport(
    Report,
    table_name="custodian_wires",
    columns=(
        "date",
        "fund",
        "custodian",
        "entry_date",
        "pay_date",
        "value_date",
        "currency",
        "amount",
        "wire_details",
        "unique_ref",
    ),
):
    date: datetime.date
    fund: Fund
    custodian: ClassVar[Custodian]
    _registry: ClassVar[dict] = {}

    def __init_subclass__(cls, custodian, **kwargs):
        cls.custodian = custodian
        cls._registry[custodian] = cls

    def get_report(self):
        report_dir = get_dir(self.date)
        pattern = f"{self.custodian}_WIRE_{self.fund}_"
        reports = [
            f
            for f in report_dir.iterdir()
            if f.name.startswith(pattern) and self.get_ts(f.name).date() == self.date
        ]
        p = max(
            reports,
            key=lambda f: self.get_ts(f.name),
            default=None,
        )
        if p:
            return p
        else:
            raise MissingDataError(
                f"Report not ready {self.date}: {self.custodian} {self.fund}"
            )

    @staticmethod
    def get_ts(s):
        m = re.search(r"\d{12}", s)
        return datetime.datetime.strptime(m[0], "%Y%m%d%H%M")


class BNYWireReport(WireReport, custodian="BNY", dtkey="%Y%m%d%H%M%S"):
    def __iter__(self):
        try:
            df = pd.read_csv(self.get_report(), thousands=",")
        except EmptyDataError as exc:
            raise EmptyDataError(
                f"File received no wires for {self.fund}: {self.custodian} {self.date}"
            ) from exc
        df["Local Amount"] = df["Local Amount"].apply(
            lambda s: "-" + s[1:-1] if s.startswith("(") else s
        )
        df["Local Amount"] = pd.to_numeric(
            df["Local Amount"].str.replace(",", "")
        )  # Not sure how to avoid this with the thousands
        df["Wire Details"] = df["Transaction Description 1"]
        df["Wire Details"] = df.where(
            df["Transaction Type Code"] == "CW", df["Transaction Description 2"]
        )
        return (
            (self.date, self.fund, self.custodian, *t)
            for t in df[
                [
                    "Cash Entry Date",
                    "Settle / Pay Date",
                    "Cash Value Date",
                    "Local Currency Code",
                    "Local Amount",
                    "Wire Details",
                    "Reference Number",
                ]
            ].itertuples(index=False)
        )

    @staticmethod
    def get_ts(s):
        m = re.search(r"\d{12}", s)
        return datetime.datetime.strptime(m[0], "%Y%m%d%H%M%S")


class NTWireReport(WireReport, custodian="NT"):
    def __iter__(self):
        df = pd.read_csv(self.get_report())
        df["Currency"] = df["N-GL-AC30"].map(
            {"EURO - EUR": "EUR", "U.S. DOLLARS - USD": "USD"}
        )
        return (
            (self.date, self.fund, self.custodian, *t)
            for t in df[
                [
                    "D-GL-POST",
                    "D-TRAN-EFF",
                    "D-TRAN-EFF",
                    "Currency",
                    "Net amount - local",
                    "narrative",
                    "C-EXTL-SYS-TRN-DSC-3",
                ]
            ].itertuples(index=False)
            if "sponsor" in t.narrative.lower()
        )


class UMBWireReport(WireReport, custodian="UMB"):
    # UMB has no unique identifier. We just delete and add back
    def __post_init__(self):
        with self._conn.cursor() as c:
            c.execute(
                "DELETE FROM custodian_wires WHERE date=%s AND fund=%s AND custodian=%s",
                (
                    self.date,
                    self.fund,
                    self.custodian,
                ),
            )
        self._conn.commit()

    def __iter__(self):
        df = pd.read_excel(self.get_report(), skiprows=3)
        df["Unique Ref"] = (
            df["Transaction Date"].astype("str") + "-" + df.index.astype("str")
        )
        if not df.iloc[0]["Transaction Date"].startswith("No records"):
            return (
                (
                    self.date,
                    self.fund,
                    self.custodian,
                    *t,
                )
                for t in df[
                    [
                        "Transaction Date",
                        "Transaction Date",
                        "Transaction Date",
                        "Local Currency Code",
                        "Net Amount",
                        "Transaction Description",
                        "Unique Ref",
                    ]
                ].itertuples(index=False)
            )
        else:
            raise EmptyDataError(
                f"File received no wires for {self.fund}: {self.custodian} {self.date}"
            )


class SCOTIAWireReport(WireReport, custodian="SCOTIA"):
    def __iter__(self):
        df = pd.read_excel(self.get_report(), skipfooter=2)
        df["Amount"] = df["Cr Amount"]
        df["Amount"] = df["Amount"].where(df["Dr/Cr"] == "Cr", -df["Amount"])
        return (
            (self.date, self.fund, self.custodian, *t)
            for t in df[
                [
                    "Posting Date",
                    "Value Date",
                    "Value Date",
                    "Curr.",
                    "Amount",
                    "Reference Data",
                    "Bank Ref.",
                ]
            ].itertuples(index=False)
        )

    def get_report(self):
        REPORT_DIR = DAILY_DIR / "Selene" / "Scotia_reports"
        return next(
            REPORT_DIR.glob(
                f"IsoSelene_{prev_business_day(self.date):%d-%b-%Y}_*_xlsx.JOAAPKO3.JOAAPKO1"
            )
        )