"""Excel file format conversion utilities.

This module provides utilities for converting between different Excel formats,
particularly converting old .xls files to modern .xlsx format using LibreOffice.

Cross-platform support for macOS and Linux.
"""

import zipfile
from pathlib import Path
import tempfile
from typing import Tuple, Optional
import structlog
import subprocess
import shutil
import os
import platform

logger = structlog.get_logger(__name__)


def _get_libreoffice_command() -> str:
    """
    Get the LibreOffice command for the current platform.

    Returns:
        Path to LibreOffice executable

    Raises:
        RuntimeError: If LibreOffice not found
    """
    system = platform.system()

    if system == 'Darwin':  # macOS
        # Check common LibreOffice locations on macOS
        possible_paths = [
            '/Applications/LibreOffice.app/Contents/MacOS/soffice',
            '/Applications/LibreOffice.app/Contents/MacOS/libreoffice',
            os.path.expanduser('~/Applications/LibreOffice.app/Contents/MacOS/soffice'),
        ]

        for path in possible_paths:
            if os.path.exists(path):
                return path

        raise RuntimeError(
            "LibreOffice not found on macOS. Please install LibreOffice:\n"
            "  brew install --cask libreoffice\n"
            "Or download from: https://www.libreoffice.org/download/"
        )

    elif system == 'Linux':
        # Try common Linux commands
        for cmd in ['libreoffice', 'soffice']:
            if shutil.which(cmd):
                return cmd

        raise RuntimeError(
            "LibreOffice not found on Linux. Please install LibreOffice:\n"
            "  Ubuntu/Debian: sudo apt-get install libreoffice\n"
            "  Fedora/RHEL: sudo dnf install libreoffice\n"
            "  Arch: sudo pacman -S libreoffice-fresh"
        )

    else:
        raise RuntimeError(f"Unsupported platform: {system}")


def convert_xls_to_xlsx(input_path: str, output_path: Optional[str] = None) -> Tuple[str, bool]:
    """
    Convert .xls or .xlsm file to .xlsx format using LibreOffice.

    This is a utility function that can be called before any Excel preprocessing
    step to ensure the file is in .xlsx format.

    Supports both macOS and Linux platforms.

    Args:
        input_path: Path to input Excel file (.xls, .xlsm, or .xlsx)
        output_path: Optional path for output .xlsx file. If None, creates temp file.

    Returns:
        Tuple of (output_file_path, was_converted)
        - output_file_path: Path to the .xlsx file
        - was_converted: True if conversion happened, False if already .xlsx

    Raises:
        RuntimeError: If conversion fails
        ValueError: If file format is not supported

    Example:
        # Convert .xls to .xlsx before processing
        xlsx_path, converted = convert_xls_to_xlsx('file.xls', 'file.xlsx')
        if converted:
            print(f"Converted to: {xlsx_path}")
    """
    input_path = Path(input_path)

    # Check if file is already .xlsx format (valid OOXML)
    is_valid_xlsx = False
    try:
        with zipfile.ZipFile(input_path, 'r') as zip_ref:
            namelist = zip_ref.namelist()
            if '[Content_Types].xml' in namelist and any('xl/workbook.xml' in f for f in namelist):
                is_valid_xlsx = True
    except (zipfile.BadZipFile, Exception):
        is_valid_xlsx = False

    # If already .xlsx, return as-is
    if is_valid_xlsx:
        if output_path:
            # Copy to output path if specified
            shutil.copy2(input_path, output_path)
            return (str(output_path), False)
        return (str(input_path), False)

    # Need to convert from .xls/.xlsm
    if input_path.suffix.lower() not in ['.xls', '.xlsm']:
        raise ValueError(
            f"File {input_path.name} is not a valid Excel file. "
            "Supported formats: .xlsx, .xlsm, .xls"
        )

    logger.info(f"Converting {input_path.name} from {input_path.suffix} to .xlsx using LibreOffice")

    # Get LibreOffice command for this platform
    libreoffice_cmd = _get_libreoffice_command()
    logger.debug(f"Using LibreOffice: {libreoffice_cmd}")

    # Create temporary directory for conversion
    with tempfile.TemporaryDirectory() as temp_dir:
        temp_path = Path(temp_dir)

        # Run LibreOffice conversion
        # --headless: run without GUI
        # --convert-to xlsx: convert to xlsx format
        # --outdir: output directory
        try:
            result = subprocess.run(
                [
                    libreoffice_cmd,
                    '--headless',
                    '--convert-to', 'xlsx',
                    '--outdir', str(temp_path),
                    str(input_path)
                ],
                capture_output=True,
                text=True,
                timeout=60
            )

            if result.returncode != 0:
                raise RuntimeError(
                    f"LibreOffice conversion failed: {result.stderr}"
                )

            # Find the converted file
            converted_files = list(temp_path.glob('*.xlsx'))
            if not converted_files:
                raise RuntimeError(
                    f"LibreOffice conversion did not produce .xlsx file"
                )

            converted_file = converted_files[0]

            # Determine final output path
            if output_path:
                final_output = Path(output_path)
            else:
                # Create persistent temp file
                final_output = Path(tempfile.mktemp(suffix='.xlsx'))

            # Copy converted file to final location
            shutil.copy2(converted_file, final_output)

            logger.info(f"Converted {input_path.name} to .xlsx: {final_output}")
            return (str(final_output), True)

        except subprocess.TimeoutExpired:
            raise RuntimeError("LibreOffice conversion timed out after 60 seconds")