"""
Data processing module for handling Excel file uploads, validation, and transformation.
"""

from typing import List, Tuple, Dict, Any, Optional
import pandas as pd
import pytz


class ValidationError:
    """Class to represent a validation error for a specific row."""

    def __init__(self, row_index: int, column: str, value: Any, error_message: str):
        self.row_index = row_index
        self.column = column
        self.value = value
        self.error_message = error_message

    def __str__(self) -> str:
        return f"Row {self.row_index + 1}, Column '{self.column}': {self.error_message} (Value: {self.value})"


class ProcessedData:
    """Container for processed data results."""

    def __init__(
        self,
        valid_rows: pd.DataFrame,
        invalid_rows: pd.DataFrame,
        errors: List[ValidationError],
    ):
        self.valid_rows = valid_rows
        self.invalid_rows = invalid_rows
        self.errors = errors

    @property
    def has_errors(self) -> bool:
        return len(self.errors) > 0

    @property
    def total_rows(self) -> int:
        return len(self.valid_rows) + len(self.invalid_rows)


def read_excel_file(uploaded_file) -> pd.DataFrame:
    """
    Read an Excel or CSV file uploaded via Streamlit and return a pandas DataFrame.

    Args:
        uploaded_file: Streamlit file uploader object

    Returns:
        pd.DataFrame: The data from the Excel or CSV file

    Raises:
        ValueError: If the file cannot be read or doesn't have expected columns
    """
    try:
        # Determine file type based on name
        file_name = uploaded_file.name.lower()

        if file_name.endswith(".csv"):
            # Try different separators for CSV files
            separators = [";", ",", "\t"]
            df = None

            for sep in separators:
                try:
                    uploaded_file.seek(0)  # Reset file position
                    df = pd.read_csv(uploaded_file, sep=sep)
                    # Check if we got meaningful columns (more than 1 column)
                    if len(df.columns) > 1:
                        break
                except Exception:
                    continue

            if df is None or len(df.columns) <= 1:
                raise ValueError("Could not parse CSV file with any common separator")

        elif file_name.endswith((".xlsx", ".xls")):
            df = pd.read_excel(uploaded_file, engine="openpyxl")
        else:
            raise ValueError(
                "Unsupported file format. Please upload .xlsx or .csv files"
            )

        # Validate that we have the expected columns
        expected_columns = ["Pub Song ID", "timestamp"]

        if df.empty:
            raise ValueError("The uploaded file is empty.")

        # Check if we have the expected columns (case-insensitive)
        missing_columns = []
        column_mapping = {}

        for expected_col in expected_columns:
            expected_lower_col = expected_col.lower()
            found = False
            for df_col in df.columns:
                if df_col.strip().lower() == expected_lower_col:
                    column_mapping[expected_col] = df_col
                    found = True
                    break
            if not found:
                missing_columns.append(expected_col)

        if missing_columns:
            raise ValueError(f"Missing required columns: {', '.join(missing_columns)}")

        # Rename columns to standardized names
        df = df.rename(columns={v: k for k, v in column_mapping.items()})

        # Keep only the required columns
        df = df[expected_columns].copy()

        return df

    except Exception as e:
        if "XLRDError" in str(type(e)):
            raise ValueError(
                "Invalid Excel file format. Please upload a valid .xlsx file."
            )
        elif "No such file" in str(e):
            raise ValueError("File could not be found or read.")
        else:
            raise ValueError(f"Error reading file: {str(e)}")


def validate_pub_song_id(value: Any) -> Tuple[bool, Optional[int]]:
    """
    Validate that a value can be converted to a positive integer (Pub Song ID).

    Args:
        value: The value to validate

    Returns:
        Tuple[bool, Optional[int]]: (is_valid, converted_value)
    """
    if pd.isna(value):
        return False, None

    try:
        # Handle float values (like 134224.0) that should be integers
        if isinstance(value, float) and value.is_integer():
            value = int(value)

        int_value = int(value)
        if int_value <= 0:
            return False, None

        return True, int_value
    except (ValueError, TypeError):
        return False, None


def validate_and_transform_timestamp(value: Any) -> Tuple[bool, Optional[str]]:
    """
    Validate and transform a timestamp to the required format: YYYY-MM-DD HH:MM

    The function expects timestamps in formats like:
    - "8/19/2025 3:20 PM EST"
    - "08/19/2025 20:20"
    - "2025-08-19 20:20"
    And converts them to UTC, then formats as required by the GraphQL API.

    Args:
        value: The timestamp value to validate and transform

    Returns:
        Tuple[bool, Optional[str]]: (is_valid, formatted_timestamp)
    """
    if pd.isna(value):
        return False, None

    try:
        # Convert to string if not already
        timestamp_str = str(value).strip()

        if not timestamp_str:
            return False, None

        # Handle explicit timezone specifications with literal UTC offsets
        # This ensures EST is treated as UTC-5 regardless of DST rules
        from datetime import timezone, timedelta

        timezone_mappings = {
            "EST": timezone(timedelta(hours=-5)),  # UTC-5
            "EDT": timezone(timedelta(hours=-4)),  # UTC-4
            "PST": timezone(timedelta(hours=-8)),  # UTC-8
            "PDT": timezone(timedelta(hours=-7)),  # UTC-7
            "CST": timezone(timedelta(hours=-6)),  # UTC-6
            "CDT": timezone(timedelta(hours=-5)),  # UTC-5
            "MST": timezone(timedelta(hours=-7)),  # UTC-7
            "MDT": timezone(timedelta(hours=-6)),  # UTC-6
        }

        # Check if timestamp has explicit timezone suffix
        specified_tz = None

        for tz_suffix, tz_offset in timezone_mappings.items():
            if timestamp_str.upper().endswith(f" {tz_suffix}"):
                specified_tz = tz_offset
                # Remove the timezone suffix for parsing
                timestamp_str = timestamp_str[: -len(f" {tz_suffix}")].strip()
                break

        # Parse the timestamp - pandas.to_datetime is quite flexible with formats
        # It should handle "8/19/2025 3:20 PM", "08/19/2025 20:20", etc.
        parsed_dt = pd.to_datetime(timestamp_str)

        # Convert to datetime object if it's a pandas Timestamp
        if hasattr(parsed_dt, "to_pydatetime"):
            parsed_dt = parsed_dt.to_pydatetime()

        # Apply timezone information
        if parsed_dt.tzinfo is None:
            if specified_tz is not None:
                # Use the explicitly specified timezone (literal offset)
                parsed_dt = parsed_dt.replace(tzinfo=specified_tz)
            else:
                # Default to America/New_York (EST/EDT) as per original plan
                ny_tz = pytz.timezone("America/New_York")
                parsed_dt = ny_tz.localize(parsed_dt)

        # Convert to UTC
        utc_dt = parsed_dt.astimezone(timezone.utc)

        # Format as required: YYYY-MM-DD HH:MM
        # Based on the existing script, we need to match this exact format
        formatted = utc_dt.strftime("%Y-%m-%d %H:%M")

        return True, formatted

    except (
        ValueError,
        TypeError,
        pytz.exceptions.AmbiguousTimeError,
        pytz.exceptions.NonExistentTimeError,
    ):
        # For debugging purposes, we might want to know what failed
        return False, None


def validate_and_transform_row(
    row: pd.Series, row_index: int
) -> Tuple[Optional[Dict[str, Any]], List[ValidationError]]:
    """
    Validate and transform a single row from the DataFrame.

    Args:
        row: A pandas Series representing one row of data
        row_index: The index of the row (for error reporting)

    Returns:
        Tuple[Optional[Dict[str, Any]], List[ValidationError]]:
        (transformed_row_dict or None, list_of_errors)
    """
    errors = []

    # Validate Pub Song ID
    pub_song_id_valid, pub_song_id = validate_pub_song_id(row["Pub Song ID"])
    if not pub_song_id_valid:
        errors.append(
            ValidationError(
                row_index=row_index,
                column="Pub Song ID",
                value=row["Pub Song ID"],
                error_message="Must be a positive integer",
            )
        )

    # Validate and transform timestamp
    timestamp_valid, formatted_timestamp = validate_and_transform_timestamp(
        row["timestamp"]
    )
    if not timestamp_valid:
        errors.append(
            ValidationError(
                row_index=row_index,
                column="timestamp",
                value=row["timestamp"],
                error_message="Must be a valid date/time that can be parsed",
            )
        )

    # If there are validation errors, return None for the row data
    if errors:
        return None, errors

    # Return the transformed row data
    return {"pubSongId": pub_song_id, "deliveryDate": formatted_timestamp}, []


def process_dataframe(df: pd.DataFrame) -> ProcessedData:
    """
    Process the entire DataFrame by validating and transforming each row.

    Args:
        df: The input DataFrame with 'Pub Song ID' and 'timestamp' columns

    Returns:
        ProcessedData: Container with valid rows, invalid rows, and error details
    """
    valid_rows_data = []
    invalid_rows_indices = []
    all_errors = []

    # Process each row
    for idx, row in df.iterrows():
        transformed_row, row_errors = validate_and_transform_row(row, idx)

        if transformed_row is not None:
            # Add the original row index for reference
            transformed_row["original_row_index"] = idx + 1
            valid_rows_data.append(transformed_row)
        else:
            invalid_rows_indices.append(idx)
            all_errors.extend(row_errors)

    # Create DataFrames for valid and invalid rows
    if valid_rows_data:
        valid_df = pd.DataFrame(valid_rows_data)
    else:
        valid_df = pd.DataFrame(
            columns=["pubSongId", "deliveryDate", "original_row_index"]
        )

    if invalid_rows_indices:
        invalid_df = df.iloc[invalid_rows_indices].copy()
        invalid_df.index = invalid_df.index + 1  # Make index 1-based for display
    else:
        invalid_df = pd.DataFrame(columns=df.columns)

    return ProcessedData(
        valid_rows=valid_df, invalid_rows=invalid_df, errors=all_errors
    )