from __future__ import annotations

from pathlib import Path
from typing import Dict, List, Optional, Union


class ExcelLoadError(Exception):
    """Raised when Excel loading fails."""


class ExcelUtil:
    """Helper for reading Excel sheets (xlsx/xlsm) using openpyxl."""

    def __init__(self, file_path: Union[str, Path]):
        self.file_path = Path(file_path)

    @staticmethod
    def _import_openpyxl():
        try:
            import openpyxl  # type: ignore
        except ImportError as exc:
            raise ExcelLoadError("openpyxl is required. Install via 'pip install openpyxl'") from exc
        return openpyxl

    def _ensure_exists(self) -> None:
        if not self.file_path.exists():
            raise ExcelLoadError(f"File not found: {self.file_path}")

    def load(
        self,
        sheet_name: Optional[str] = None,
        has_header: bool = True,
    ) -> List[Union[Dict[str, object], List[object]]]:
        """
        Load data from the Excel file into a list.

        Args:
            sheet_name: Sheet to read; defaults to the first sheet.
            has_header: When True, return a list of dict rows keyed by header row; otherwise return list of lists.

        Raises:
            ExcelLoadError: When the file is missing, not readable, or openpyxl is unavailable.
        """
        self._ensure_exists()
        openpyxl = self._import_openpyxl()

        try:
            wb = openpyxl.load_workbook(self.file_path, data_only=True, read_only=True)
        except Exception as exc:
            raise ExcelLoadError(f"Failed to open Excel file: {exc}") from exc

        ws = wb[sheet_name] if sheet_name else wb.active
        rows = list(ws.iter_rows(values_only=True))

        if not rows:
            return []

        if not has_header:
            return [list(row) for row in rows]

        headers = [str(h).strip() if h is not None else "" for h in rows[0]]
        data_rows = rows[1:]
        result: List[Dict[str, object]] = []
        for row in data_rows:
            row_dict = {headers[i] if i < len(headers) else f"col{i}": row[i] for i in range(len(row))}
            result.append(row_dict)
        return result

    def list_sheets(self) -> List[str]:
        """Return available sheet names for the current file."""
        self._ensure_exists()
        openpyxl = self._import_openpyxl()
        try:
            wb = openpyxl.load_workbook(self.file_path, read_only=True)
            return wb.sheetnames
        except Exception as exc:
            raise ExcelLoadError(f"Failed to read sheet names: {exc}") from exc

    def find_value_by_column(
        self,
        key_column: str,
        key_value: object,
        value_column: str,
        sheet_name: Optional[str] = None,
    ) -> Optional[object]:
        """
        Return the first value in column `value_column` where column `key_column` equals `key_value`.

        Both column arguments should match header names when `has_header=True`.
        """
        rows = self.load(sheet_name=sheet_name, has_header=True)
        for row in rows:
            if row.get(key_column) == key_value:
                return row.get(value_column)
        return None

    def map_rows(self, sheet_name: Optional[str], column_map: Dict[str, str]) -> List[Dict[str, object]]:
        """
        Load rows as dicts (header->value) and remap keys using column_map.

        column_map: {new_key: header_name_in_excel}
        Unmapped headers are ignored; missing headers yield None.
        """
        rows = self.load(sheet_name=sheet_name, has_header=True)
        mapped: List[Dict[str, object]] = []
        for row in rows:
            mapped_row = {new_key: row.get(header) for new_key, header in column_map.items()}
            mapped.append(mapped_row)
        return mapped

    @classmethod
    def load_excel(
        cls,
        file_path: Union[str, Path],
        sheet_name: Optional[str] = None,
        has_header: bool = True,
    ) -> List[Union[Dict[str, object], List[object]]]:
        """Convenience classmethod wrapper for one-off loads."""
        return cls(file_path).load(sheet_name=sheet_name, has_header=has_header)

    @classmethod
    def list_excel_sheets(cls, file_path: Union[str, Path]) -> List[str]:
        """Convenience classmethod wrapper to list sheet names for a path."""
        return cls(file_path).list_sheets()

    @classmethod
    def find_value_by_column_excel(
        cls,
        file_path: Union[str, Path],
        key_column: str,
        key_value: object,
        value_column: str,
        sheet_name: Optional[str] = None,
    ) -> Optional[object]:
        """Convenience wrapper for find_value_by_column on a file path."""
        return cls(file_path).find_value_by_column(
            key_column=key_column,
            key_value=key_value,
            value_column=value_column,
            sheet_name=sheet_name,
        )

    @classmethod
    def load_mapped_excel(
        cls,
        file_path: Union[str, Path],
        sheet_name: Optional[str],
        column_map: Dict[str, str],
    ) -> List[Dict[str, object]]:
        """Convenience wrapper to load rows and remap headers to new keys."""
        return cls(file_path).map_rows(sheet_name=sheet_name, column_map=column_map)


if __name__ == "__main__":
    # 一次性调用
    rows = ExcelUtil.load_excel("data/rules.xlsx", sheet_name=None, has_header=False)
    print(rows)
