from mm_stats.auth import PostgresDb as PostCon
import pandas as pd


def get_table(sql: str, database: str = "postgres") -> pd.DataFrame:
    """
    Request a Table from Database and return it as DataFrame.

    Parameters
    ----------
    sql: str
        sql specifying table and parameters
    database: str
        which db is to be used
    Returns
    -------
    DataFrame
    """
    db = PostCon()
    df = pd.read_sql_query(sql=sql, con=db.db_connection)
    del db
    return df


def resample_by_datetime(
    df: pd.DataFrame, date_column: str, span: str, max_date: str
) -> pd.DataFrame:
    """
    Aggregate df on a date column. !!!date_column is index after function.

    Parameters
    ----------
    df: DataFrame
        input DataFrame
    date_column: str
        name of date column
    span: str
        time period eg. aggregate monthly ("M") or yearly ("Y")
    max_date: str
        maximum date which should be used in plot
        Format: 'yyyy-mm-dd'
    Returns
    -------
        DataFrame aggregated on date column
    """
    df[date_column] = pd.to_datetime(df[date_column], format="%Y-%m-%dT%H:%M:%S.%f")
    df = df[df[date_column] <= "{} 00:00:00".format(max_date)]
    df.set_index(date_column, drop=True, inplace=True)
    df = df.resample(span)
    return df


def seconds_to_x(
    unit: str, value: int = 1, df: pd.DataFrame = None, column: str = None
):
    """Convert seconds as int value or df column unit."""
    units = {
        "second": 1,
        "minute": 60,
        "hour": 60 * 60,
        "day": 60 * 60 * 24,
        "month": 60 * 60 * 24 * 30,
        "year": 60 * 60 * 24 * 30 * 12,
    }
    if df is not None:
        df[column] = df[column] / units[unit]
        return df
    else:
        return value / units[unit]


def timedelta_to_num(
    df: pd.DataFrame, columns: list, unit: str = "second"
) -> pd.DataFrame:  # noqa: D410,D411 -> black formats like this but then flake8 cries.
    """
    Convert timedelta to given unit as integer value.

    Parameters
    ----------
    df: pd.Dataframe
        Dataframe containing the columns
    columns: list
        list of column names
    unit: str
        TimeFrame. Valid Values: [second, minute, hour, day, month, year]
    Returns
    -------
    DataFrame
    """
    for column in columns:
        df[column] = df[column].dt.total_seconds()
        df = seconds_to_x(unit, df=df, column=column)

    return df