"""Excel file loader with .xls format support.

Provides a unified interface for loading Excel files in any format:
- .xlsx (Office Open XML) - via openpyxl (preferred)
- .xlsm (with macros) - via openpyxl
- .xls (legacy Excel 97-2003) - via pandas fallback

The loader tries openpyxl first for best format preservation,
then falls back to pandas for .xls files.
"""

import tempfile
from pathlib import Path
from typing import Optional

import structlog
from openpyxl import load_workbook, Workbook

logger = structlog.get_logger(__name__)


def load_excel_file(file_path: str, read_only: bool = False, data_only: bool = False) -> Optional[Workbook]:
    """Load an Excel file with automatic format detection and fallback.

    Tries to load with openpyxl first (best for .xlsx/.xlsm), falls back
    to pandas for legacy .xls files.

    Args:
        file_path: Path to Excel file (.xlsx, .xlsm, or .xls)
        read_only: Open in read-only mode (faster, less memory)
        data_only: Read only cell values, not formulas

    Returns:
        openpyxl Workbook object, or None if loading fails

    Raises:
        Exception: If file cannot be loaded with either method
    """
    file_ext = Path(file_path).suffix.lower()

    # Try openpyxl first (works for .xlsx and .xlsm)
    if file_ext in ['.xlsx', '.xlsm']:
        try:
            logger.debug(f"Loading {file_ext} file with openpyxl: {file_path}")
            wb = load_workbook(file_path, read_only=read_only, data_only=data_only)
            logger.info(f"Successfully loaded {file_ext} file with openpyxl")
            return wb
        except Exception as e:
            logger.warning(f"openpyxl failed for {file_ext} file: {e}")
            # Fall through to pandas fallback

    # For .xls files OR if openpyxl failed, try pandas
    if file_ext == '.xls' or True:  # Always try pandas as fallback
        try:
            logger.info(f"Attempting to load {file_ext} file with pandas fallback...")
            wb = _load_with_pandas(file_path)
            if wb:
                logger.info(f"Successfully loaded {file_ext} file with pandas → openpyxl conversion")
                return wb
        except Exception as e:
            logger.error(f"pandas fallback failed for {file_ext}: {e}")

    # Both methods failed
    logger.error(f"Failed to load Excel file {file_path} with both openpyxl and pandas")
    return None


def _load_with_pandas(file_path: str) -> Optional[Workbook]:
    """Load .xls file using pandas and convert to openpyxl Workbook.

    Args:
        file_path: Path to .xls file

    Returns:
        openpyxl Workbook object or None if conversion fails
    """
    try:
        import pandas as pd
        from openpyxl.utils.dataframe import dataframe_to_rows

        logger.debug(f"Reading .xls file with pandas: {file_path}")

        # Read all sheets from .xls file
        excel_file = pd.ExcelFile(file_path, engine='xlrd')
        sheet_names = excel_file.sheet_names

        logger.debug(f"Found {len(sheet_names)} sheets in .xls file: {sheet_names}")

        # Create new openpyxl workbook
        wb = Workbook()
        wb.remove(wb.active)  # Remove default sheet

        # Convert each sheet to openpyxl worksheet
        for sheet_name in sheet_names:
            df = excel_file.parse(sheet_name, header=None)

            # Create worksheet
            ws = wb.create_sheet(title=sheet_name)

            # Write dataframe to worksheet
            for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), start=1):
                for c_idx, value in enumerate(row, start=1):
                    # Handle pandas NaN/None
                    if pd.isna(value):
                        value = None
                    ws.cell(row=r_idx, column=c_idx, value=value)

            logger.debug(f"Converted sheet '{sheet_name}': {df.shape[0]} rows × {df.shape[1]} cols")

        logger.info(f"Successfully converted .xls to openpyxl Workbook ({len(sheet_names)} sheets)")
        return wb

    except ImportError:
        logger.error(
            "pandas or xlrd not installed. "
            "Install with: pip install pandas xlrd"
        )
        return None
    except Exception as e:
        logger.error(f"Failed to convert .xls file with pandas: {e}", exc_info=True)
        return None


def convert_xls_to_xlsx(xls_path: str, xlsx_output_path: Optional[str] = None) -> Optional[str]:
    """Convert a .xls file to .xlsx format.

    Useful for one-time batch conversions or pre-processing pipelines.

    Args:
        xls_path: Path to input .xls file
        xlsx_output_path: Optional path for output .xlsx file
                         (defaults to same name with .xlsx extension)

    Returns:
        Path to created .xlsx file, or None if conversion fails
    """
    if xlsx_output_path is None:
        xls_file = Path(xls_path)
        xlsx_output_path = str(xls_file.with_suffix('.xlsx'))

    try:
        logger.info(f"Converting .xls to .xlsx: {xls_path} → {xlsx_output_path}")

        # Load .xls with pandas
        wb = _load_with_pandas(xls_path)

        if wb is None:
            logger.error("Failed to load .xls file")
            return None

        # Save as .xlsx
        wb.save(xlsx_output_path)
        logger.info(f"Successfully converted .xls to .xlsx: {xlsx_output_path}")

        return xlsx_output_path

    except Exception as e:
        logger.error(f"Conversion failed: {e}", exc_info=True)
        return None