"""Model for interacting with Snowflake to fetch takedown data and ISRC track data."""

import pandas as pd
from src.connectors import snowflake as snowflake_connector
from src.sql import snowflake as snowflake_query
from src.utils import constants


def fetch_raw_data(last_run: str, current_run: str) -> pd.DataFrame:
    """Load takedown data from Snowflake and return as a DataFrame.

    Args:
        last_run: Start timestamp (YYYY-MM-DD HH:MM:SS) for the date filter.
        current_run: End timestamp (YYYY-MM-DD HH:MM:SS) for the date filter.

    Returns:
        DataFrame with takedown data sorted by Release UPC.
    """
    try:
        query = snowflake_query.META_UPDATE_QUEUE_QUERY.format(
            last_run=last_run,
            current_run=current_run,
        )
        results = snowflake_connector.execute_query(query)
        takedown_df = pd.DataFrame(results, columns=constants.TAKEDOWN_COLUMNS)
        takedown_df[constants.RELEASE_UPC] = takedown_df[constants.RELEASE_UPC].astype(str)
        return takedown_df.sort_values(constants.RELEASE_UPC).reset_index(drop=True)
    except Exception as e:
        raise RuntimeError(f'Unexpected error while fetching raw takedown data: {e}') from e


def fetch_isrc_track_data(takedown_df: pd.DataFrame) -> pd.DataFrame:
    """Fetch track data from Snowflake for deduplicated ISRCs and return as a DataFrame."""
    try:
        isrcs = takedown_df['Track ISRC'].dropna().drop_duplicates().tolist()

        if not isrcs:
            return pd.DataFrame(columns=constants.ISRC_LOOKUP_COLUMNS)

        isrc_list = ', '.join(f"'{isrc}'" for isrc in isrcs)
        query = snowflake_query.ISRC_LOOKUP_QUERY.format(isrc_list=isrc_list)
        results = snowflake_connector.execute_query(query)

        isrc_df = pd.DataFrame(results, columns=constants.ISRC_LOOKUP_COLUMNS)
        isrc_df['Display UPC'] = isrc_df['Display UPC'].astype(str)
        return isrc_df
    except Exception as e:
        raise RuntimeError(f'Unexpected error while fetching ISRC track data: {e}') from e