"""Excel/CSV data loading and parsing."""

from __future__ import annotations
from pathlib import Path
import pandas as pd


def load_file(filepath: str | Path) -> dict[str, pd.DataFrame]:
    """Load an Excel or CSV file into a dict of DataFrames.

    Returns:
        Dict mapping sheet names (or "_default" for CSV) to DataFrames.
    """
    filepath = Path(filepath)

    if filepath.suffix.lower() in (".xlsx", ".xls"):
        return _load_excel(filepath)
    elif filepath.suffix.lower() == ".csv":
        df = pd.read_csv(filepath)
        return {"_default": df}
    else:
        raise ValueError(f"Unsupported file type: {filepath.suffix}")


def _load_excel(filepath: Path) -> dict[str, pd.DataFrame]:
    """Load all sheets from an Excel file."""
    xls = pd.ExcelFile(filepath)
    result = {}

    for sheet_name in xls.sheet_names:
        try:
            df = pd.read_excel(xls, sheet_name=sheet_name)
            # Skip empty or tiny sheets
            if df.empty or (df.shape[0] < 2 and df.shape[1] < 2):
                continue
            # Clean up: drop fully empty rows/columns
            df = df.dropna(how="all").dropna(axis=1, how="all")
            if not df.empty:
                result[sheet_name] = df
        except Exception:
            continue

    return result