import logging
import os

import pandas as pd
import snowflake
from snowflake.connector import pandas_tools

logger = logging.getLogger(__file__)

# key: column name in Excel
# value: column name in Snowflake
# IMPORTANT: use ALL CAPITAL for Snowflake values
LATAM_UNIFIED_WEEKLY_CHART_COLUMNS = {
    'Position': 'POSITION',
    'Pos. before': 'POS_BEFORE',
    'Pos. 2 before': 'POS_2_BEFORE',
    'Total times': 'TOTAL_TIMES',
    'Track': 'TRACK',
    'Artist': 'ARTIST',
    'ISRCs': 'ISRCS',
    'Release Date': 'RELEASE_DATE',
    'Previous amount': 'PREVIOUS_AMOUNT',
    '%': 'PERCENT',
    'Current amount': 'CURRENT_AMOUNT',
    'Label': 'LABEL',
    'Peak': 'PEAK',
    'Accumulated': 'ACCUMULATED',
    'Year': 'YEAR',
    'Week': 'WEEK',
    'Country': 'COUNTRY'
}


def load_latam_unified_weekly_chart(xlsx_file) -> [pd.DataFrame, int, int]:
    df = pd.read_excel(xlsx_file, skiprows=4, header=1, usecols='B:R')
    columns = list(df.columns)
    expected_columns = list(LATAM_UNIFIED_WEEKLY_CHART_COLUMNS.keys())
    if columns != expected_columns:
        raise ValueError(f'Incompatible columns. Expected: {expected_columns}, received {columns}')

    # updates
    df.rename(columns=LATAM_UNIFIED_WEEKLY_CHART_COLUMNS, inplace=True, errors="raise")

    df.replace(['-'], None, inplace=True)

    week_values = df['WEEK'].unique()
    year_values = df['YEAR'].unique()
    if len(week_values) != 1 or len(year_values) != 1:
        raise ValueError(f'Only one week per file! Week: {week_values} Year: {year_values}')
    year = int(year_values[0])
    week = int(week_values[0])
    if not year:
        raise ValueError(f'Year is undefined: "{year}"')
    if not week:
        raise ValueError(f'Week is undefined: "{week}"')
    return df, year, week


def save_dataframe_to_table(connection: snowflake.connector.SnowflakeConnection, df: pd.DataFrame, table_name: str) -> [bool, int]:
    table_name = table_name.upper()
    success, nchunks, nrows, message = pandas_tools.write_pandas(
        conn=connection,
        df=df,
        table_name=table_name,
        table_type='transient',
        auto_create_table=True,
        overwrite=True,
    )
    logger.warning(f'Is success: {success}, Number of rows: {nrows}, Message: {message}')
    return success, nrows