"""Custom loader for legacy ``.xls`` (BIFF) spreadsheets.

``unstructured``'s Excel partitioner runs a ``msoffcrypto`` "is encrypted?"
pre-check on every workbook. That check crashes on some legacy ``.xls`` files
(``struct.error: unpack requires a buffer of 4 bytes`` in its xls97 record
reader) even though ``xlrd`` reads the same file fine. To make ``.xls`` uploads
extract reliably, this loader reads the workbook directly with pandas + xlrd
and bypasses that pre-check. Modern ``.xlsx`` files still use the unstructured
loader, whose OOXML path is unaffected.
"""

from pathlib import Path
from typing import Iterator

import pandas as pd
from langchain_core.document_loaders import BaseLoader
from langchain_core.documents import Document
from loguru import logger


class XLSLoader(BaseLoader):
    """Load a legacy ``.xls`` workbook and convert each sheet to text.

    Every sheet is read with the ``xlrd`` engine and rendered as one
    space-joined line per row (skipping empty cells), which mirrors the plain
    text that the unstructured Excel loader produces for ``.xlsx``.
    """

    def __init__(self, file_path: str | Path, **kwargs: object):
        self.file_path = Path(file_path)

    def lazy_load(self) -> Iterator[Document]:
        try:
            # sheet_name=None reads all sheets; header=None keeps the first row
            # as data so column headers are included in the extracted text.
            sheets = pd.read_excel(
                self.file_path,
                sheet_name=None,
                header=None,
                engine="xlrd",
            )
        except Exception as exc:
            msg = str(exc).lower()
            if "encrypt" in msg or "password" in msg:
                raise ValueError(
                    f"XLS file is encrypted and cannot be read: {self.file_path}"
                ) from exc
            logger.exception(f"Error loading XLS file: {self.file_path}")
            raise

        for sheet_name, frame in sheets.items():
            lines = []
            for row in frame.itertuples(index=False, name=None):
                cells = [str(value) for value in row if pd.notna(value)]
                if cells:
                    lines.append(" ".join(cells))
            text = "\n".join(lines).strip()
            if not text:
                continue
            yield Document(
                page_content=text,
                metadata={
                    "source": str(self.file_path),
                    "file_type": "xls",
                    "sheet_name": sheet_name,
                },
            )

    def load(self) -> list[Document]:
        """Load all sheets from the XLS file."""
        return list(self.lazy_load())
