"""Google Sheets client for reading KPI data."""

from __future__ import annotations

from dataclasses import dataclass
from pathlib import Path
from typing import Any

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build


@dataclass
class SheetData:
    """Container for sheet data."""

    headers: list[str]
    rows: list[dict[str, Any]]
    raw: list[list[Any]]

    def to_period_dict(self, period_column: str = "period") -> dict[str, dict[str, float]]:
        """Convert to period-based dictionary.

        Returns:
            Dictionary mapping period to column values.
            Example: {"2024-01": {"leads": 1000, "revenue": 50000}}
        """
        result: dict[str, dict[str, float]] = {}

        for row in self.rows:
            period = row.get(period_column)
            if not period:
                continue

            values: dict[str, float] = {}
            for key, value in row.items():
                if key == period_column:
                    continue
                parsed = self._parse_number(value)
                if parsed is not None:
                    values[key] = parsed

            result[str(period)] = values

        return result

    @staticmethod
    def _parse_number(value: Any) -> float | None:
        """Parse value to number."""
        if value is None or value == "":
            return None

        try:
            # Handle comma-separated numbers and percentages
            clean = str(value).replace(",", "").replace("%", "").strip()
            return float(clean)
        except (ValueError, TypeError):
            return None


class GoogleSheetsClient:
    """Google Sheets API client."""

    SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"]

    def __init__(self, credentials_path: str | Path):
        """Initialize with service account credentials.

        Args:
            credentials_path: Path to service account JSON file.
        """
        self.credentials_path = Path(credentials_path)
        self._service = None

    @property
    def service(self) -> Any:
        """Lazy-load the Sheets service."""
        if self._service is None:
            creds = Credentials.from_service_account_file(
                str(self.credentials_path), scopes=self.SCOPES
            )
            self._service = build("sheets", "v4", credentials=creds)
        return self._service

    def read_range(self, sheet_id: str, range_name: str) -> SheetData:
        """Read data from a sheet range.

        Args:
            sheet_id: Google Sheets ID.
            range_name: Range in A1 notation (e.g., "Sheet1!A1:Z100").

        Returns:
            SheetData containing headers, rows, and raw data.
        """
        result = (
            self.service.spreadsheets()
            .values()
            .get(spreadsheetId=sheet_id, range=range_name)
            .execute()
        )

        values = result.get("values", [])

        if not values:
            return SheetData(headers=[], rows=[], raw=[])

        headers = [str(h).strip() for h in values[0]]
        rows: list[dict[str, Any]] = []

        for row_values in values[1:]:
            row_dict: dict[str, Any] = {}
            for i, header in enumerate(headers):
                if i < len(row_values):
                    row_dict[header] = row_values[i]
                else:
                    row_dict[header] = None
            rows.append(row_dict)

        return SheetData(headers=headers, rows=rows, raw=values)

    def get_kpi_data(
        self,
        sheet_id: str,
        data_range: str,
        target_range: str | None = None,
        period_column: str = "period",
        pivot_format: bool = False,
        pivot_config: dict | None = None,
    ) -> tuple[dict[str, dict[str, float]], dict[str, dict[str, float]]]:
        """Get KPI actual and target data.

        Args:
            sheet_id: Google Sheets ID.
            data_range: Range for actual data.
            target_range: Optional range for target data.
            period_column: Column name for period.
            pivot_format: If True, parse as pivot table format.
            pivot_config: Configuration for pivot format parsing.

        Returns:
            Tuple of (actual_data, target_data) as period-based dictionaries.
        """
        if pivot_format:
            return self.get_kpi_data_pivot(sheet_id, data_range, pivot_config or {})

        # Read actual data
        actual_sheet = self.read_range(sheet_id, data_range)
        actual_data = actual_sheet.to_period_dict(period_column)

        # Read target data if provided
        target_data: dict[str, dict[str, float]] = {}
        if target_range:
            target_sheet = self.read_range(sheet_id, target_range)
            target_data = target_sheet.to_period_dict(period_column)

        return actual_data, target_data

    def get_kpi_data_pivot(
        self,
        sheet_id: str,
        data_range: str,
        config: dict,
    ) -> tuple[dict[str, dict[str, float]], dict[str, dict[str, float]]]:
        """Parse pivot table format sheet.

        Expected format:
        - Row with periods (e.g., 2025-04, 2025-05, ...)
        - Row with headers (e.g., 予算, 実績, 差異, 達成率) repeated for each period
        - Data rows with item names and values

        Args:
            sheet_id: Google Sheets ID.
            data_range: Range to read.
            config: Pivot configuration with:
                - period_row: Row index for periods (0-based), default 3
                - header_row: Row index for headers (0-based), default 4
                - data_start_row: Row index where data starts, default 5
                - item_col: Column index for item names, default 1
                - data_start_col: Column index where data starts, default 4
                - cols_per_period: Number of columns per period, default 4
                - budget_col_offset: Offset for budget column within period, default 0
                - actual_col_offset: Offset for actual column within period, default 1
                - item_mapping: Mapping of sheet item names to KPI IDs

        Returns:
            Tuple of (actual_data, target_data) as period-based dictionaries.
        """
        sheet_data = self.read_range(sheet_id, data_range)
        raw = sheet_data.raw

        if not raw:
            return {}, {}

        # Get config values with defaults
        period_row = config.get("period_row", 3)
        header_row = config.get("header_row", 4)
        data_start_row = config.get("data_start_row", 5)
        item_col = config.get("item_col", 1)
        data_start_col = config.get("data_start_col", 4)
        cols_per_period = config.get("cols_per_period", 4)
        budget_col_offset = config.get("budget_col_offset", 0)
        actual_col_offset = config.get("actual_col_offset", 1)
        item_mapping = config.get("item_mapping", {})

        # Extract periods from period row
        periods: list[str] = []
        period_cols: list[int] = []

        if len(raw) > period_row:
            period_row_data = raw[period_row]
            col = data_start_col
            while col < len(period_row_data):
                period_val = period_row_data[col] if col < len(period_row_data) else ""
                if period_val and str(period_val).strip():
                    periods.append(str(period_val).strip())
                    period_cols.append(col)
                col += cols_per_period

        if not periods:
            return {}, {}

        # Initialize result dictionaries
        actual_data: dict[str, dict[str, float]] = {p: {} for p in periods}
        target_data: dict[str, dict[str, float]] = {p: {} for p in periods}

        # Parse data rows
        for row_idx in range(data_start_row, len(raw)):
            row = raw[row_idx]
            if len(row) <= item_col:
                continue

            item_name = str(row[item_col]).strip() if row[item_col] else ""
            if not item_name:
                continue

            # Map item name to KPI ID if mapping exists
            kpi_id = item_mapping.get(item_name, item_name)

            # Extract values for each period
            for i, (period, col) in enumerate(zip(periods, period_cols)):
                budget_col = col + budget_col_offset
                actual_col = col + actual_col_offset

                budget_val = self._parse_cell_value(row, budget_col)
                actual_val = self._parse_cell_value(row, actual_col)

                if budget_val is not None:
                    target_data[period][kpi_id] = budget_val
                if actual_val is not None:
                    actual_data[period][kpi_id] = actual_val

        return actual_data, target_data

    @staticmethod
    def _parse_cell_value(row: list, col: int) -> float | None:
        """Parse a cell value to float."""
        if col >= len(row):
            return None

        value = row[col]
        if value is None or value == "":
            return None

        try:
            clean = str(value).replace(",", "").replace("%", "").strip()
            return float(clean)
        except (ValueError, TypeError):
            return None


class MockSheetsClient:
    """Mock client for testing without Google API."""

    def __init__(self, data: dict[str, dict[str, float]] | None = None):
        """Initialize with optional mock data."""
        self.data = data or {}
        self.targets: dict[str, dict[str, float]] = {}

    def set_data(self, data: dict[str, dict[str, float]]) -> None:
        """Set mock actual data."""
        self.data = data

    def set_targets(self, targets: dict[str, dict[str, float]]) -> None:
        """Set mock target data."""
        self.targets = targets

    def get_kpi_data(
        self,
        sheet_id: str,
        data_range: str,
        target_range: str | None = None,
        period_column: str = "period",
    ) -> tuple[dict[str, dict[str, float]], dict[str, dict[str, float]]]:
        """Return mock data."""
        return self.data, self.targets