"""Record builder for converting table rows into structured records."""

from datetime import date, datetime
from typing import Any
from uuid import UUID

import structlog
from openpyxl.utils import get_column_letter, column_index_from_string

from src.extraction.models import (
    TableBoundary,
    HeaderInfo,
    MergeMetadata,
    ExtractedRecord,
)
from src.core.exceptions import ExtractionError

logger = structlog.get_logger(__name__)


class RecordBuilder:
    """Builds structured records from resolved table grid and headers.

    Converts table rows into ExtractedRecord objects with proper data types,
    source metadata, and merge metadata integration.
    """

    # Keywords that indicate summary/total rows
    SUMMARY_KEYWORDS = [
        "total",
        "sum",
        "average",
        "subtotal",
        "count",
        "grand total",
        "sub-total",
    ]

    def build_records(
        self,
        resolved_grid: list[list[Any]],
        headers: dict[str, HeaderInfo],
        merge_metadata: list[MergeMetadata],
        table_boundary: TableBoundary,
        document_id: UUID | str,
        filename: str,
    ) -> list[ExtractedRecord]:
        """Build structured records from resolved grid and headers.

        Args:
            resolved_grid: 2D list where resolved[row_idx][col_idx] = cell value
            headers: Dictionary mapping column letters to HeaderInfo objects
            merge_metadata: List of MergeMetadata from merged cell resolution
            table_boundary: TableBoundary defining the table region
            document_id: UUID of the source document
            filename: Original filename

        Returns:
            List of ExtractedRecord objects

        Raises:
            ExtractionError: If record building fails
        """
        try:
            logger.info(
                "record_building_started",
                sheet_name=table_boundary.sheet,
                table_id=table_boundary.table_id,
                grid_rows=len(resolved_grid),
                header_count=len(headers),
            )

            # Convert document_id to string if UUID
            if isinstance(document_id, UUID):
                document_id = str(document_id)

            # Build merge metadata lookup by cell coordinate
            merge_lookup = self._build_merge_lookup(merge_metadata)

            # Get header depth to skip header rows
            header_depth = max(h.depth for h in headers.values()) if headers else 0

            # Build column letter mapping from physical table boundary (not len(headers))
            # This ensures correct column-to-value alignment even if some columns have no headers
            start_col_idx = column_index_from_string(table_boundary.start_col)
            end_col_idx = column_index_from_string(table_boundary.end_col)
            col_letters = [
                get_column_letter(col_idx) for col_idx in range(start_col_idx, end_col_idx + 1)
            ]

            # Extract known header names for repeated header detection
            known_headers = {h.display for h in headers.values()}

            records = []
            rows_skipped = 0

            # Process each data row (skip header rows)
            for row_idx in range(header_depth, len(resolved_grid)):
                row_values = resolved_grid[row_idx]

                # Skip empty rows
                if self._is_empty_row(row_values):
                    rows_skipped += 1
                    logger.debug(
                        "skipped_empty_row",
                        row=table_boundary.start_row + row_idx,
                        reason="all_cells_empty",
                    )
                    continue

                # Skip summary/total rows
                if self._is_summary_row(row_values):
                    rows_skipped += 1
                    logger.debug(
                        "skipped_summary_row",
                        row=table_boundary.start_row + row_idx,
                        reason="contains_summary_keyword",
                    )
                    continue

                # Skip repeated header rows
                if self._is_repeated_header(row_values, known_headers):
                    rows_skipped += 1
                    logger.debug(
                        "skipped_repeated_header",
                        row=table_boundary.start_row + row_idx,
                        reason="matches_header_values",
                    )
                    continue

                # Build content dict mapping headers to values
                content = {}
                record_merge_metadata = {}

                for col_idx, col_letter in enumerate(col_letters):
                    if col_letter not in headers:
                        continue

                    header_name = headers[col_letter].display
                    raw_value = (
                        row_values[col_idx] if col_idx < len(row_values) else None
                    )

                    # Convert cell value to appropriate type
                    typed_value = self._convert_cell_type(raw_value)
                    content[header_name] = typed_value

                    # Check for merge metadata for this cell
                    physical_row = table_boundary.start_row + row_idx
                    cell_coord = f"{col_letter}{physical_row}"
                    if cell_coord in merge_lookup:
                        record_merge_metadata[header_name] = merge_lookup[
                            cell_coord
                        ].merged_from_range

                # Skip if content is empty (all values are None/empty)
                if not content or all(v is None or v == "" for v in content.values()):
                    rows_skipped += 1
                    logger.debug(
                        "skipped_empty_row",
                        row=table_boundary.start_row + row_idx,
                        reason="empty_content",
                    )
                    continue

                # Build source metadata
                physical_row = table_boundary.start_row + row_idx
                col_range = f"{table_boundary.start_col}:{table_boundary.end_col}"
                source = {
                    "document_id": document_id,
                    "filename": filename,
                    "sheet": table_boundary.sheet,
                    "table_id": table_boundary.table_id,
                    "table_title": table_boundary.title,
                    "row": physical_row,
                    "col_range": col_range,
                }

                # Create record
                record = ExtractedRecord(
                    content=content,
                    headers=list(content.keys()),
                    _source=source,
                    _merge_metadata=record_merge_metadata if record_merge_metadata else None,
                )
                records.append(record)

            logger.info(
                "record_building_completed",
                sheet_name=table_boundary.sheet,
                table_id=table_boundary.table_id,
                records_built=len(records),
                rows_skipped=rows_skipped,
            )

            return records

        except Exception as e:
            logger.error(
                "record_building_failed",
                sheet_name=table_boundary.sheet,
                table_id=table_boundary.table_id,
                error=str(e),
            )
            raise ExtractionError(
                message=f"Failed to build records for table {table_boundary.table_id}",
                details={"sheet": table_boundary.sheet, "error": str(e)},
            )

    def _build_merge_lookup(
        self, merge_metadata: list[MergeMetadata]
    ) -> dict[str, MergeMetadata]:
        """Build lookup dictionary of cell coordinates to merge metadata.

        Args:
            merge_metadata: List of MergeMetadata objects

        Returns:
            Dictionary mapping cell coordinates to MergeMetadata
        """
        return {meta.cell_coordinate: meta for meta in merge_metadata}

    def _convert_cell_type(self, value: Any) -> Any:
        """Convert openpyxl cell value to appropriate Python type.

        Args:
            value: Raw cell value from openpyxl

        Returns:
            Converted value with appropriate type
        """
        # None or empty string → null
        if value is None or value == "":
            return None

        # Already correct type (openpyxl handles most)
        if isinstance(value, (int, float, bool)):
            return value

        # Date/datetime → ISO 8601 string
        if isinstance(value, (datetime, date)):
            return value.isoformat()

        # Everything else → string (strip whitespace)
        return str(value).strip()

    def _is_empty_row(self, row_values: list[Any]) -> bool:
        """Detect if row is completely empty.

        Args:
            row_values: List of cell values in the row

        Returns:
            True if all cells are None or empty string
        """
        return all(v is None or v == "" for v in row_values)

    def _is_summary_row(self, row_values: list[Any]) -> bool:
        """Detect if row is a summary/total row.

        Args:
            row_values: List of cell values in the row

        Returns:
            True if row contains summary keywords
        """
        # Check first few cells for summary keywords
        for value in row_values[:3]:
            if value and isinstance(value, str):
                value_lower = value.lower()
                if any(keyword in value_lower for keyword in self.SUMMARY_KEYWORDS):
                    return True
        return False

    def _is_repeated_header(
        self, row_values: list[Any], known_headers: set[str]
    ) -> bool:
        """Detect if row repeats header values.

        Args:
            row_values: List of cell values in the row
            known_headers: Set of known header names

        Returns:
            True if row appears to be a repeated header
        """
        if not row_values:
            return False

        # Count how many row values match known headers
        matches = sum(1 for v in row_values if v and str(v) in known_headers)

        # If 70% or more match, likely a repeated header
        threshold = len(row_values) * 0.7
        return matches >= threshold