"""Symbol dictionary detection for Excel workbooks.

This module provides functionality to automatically detect sheets that
define symbol meanings (legend sheets, code dictionaries, etc.) and
extract symbol-meaning mappings.
"""

import re
from typing import Any, Optional
from uuid import UUID

import structlog
from openpyxl import Workbook
from openpyxl.cell.cell import Cell, MergedCell
from openpyxl.worksheet.worksheet import Worksheet
from sqlalchemy.orm import Session

from src.db import models as db_models
from src.extraction.models import (
    DetectedDictionary,
    SymbolDetectionSummary,
    SymbolEntry,
)

logger = structlog.get_logger(__name__)

# Inline legend detection patterns
# Matches: 凡例）○：タグあり、－：タグなし
INLINE_LEGEND_PATTERN = re.compile(
    r"凡例[）\)]\s*(.*)",
    re.UNICODE,
)

# Pattern to extract symbol:meaning pairs from legend text
# Matches: ○：タグあり or ○:text or ○=meaning
SYMBOL_MEANING_PATTERN = re.compile(
    r"([○×△◎－\-\*●□■◇◆✓✗☐☑])\s*[：:＝=]\s*([^、,○×△◎－\-\*●□■◇◆✓✗☐☑]+)",
    re.UNICODE,
)

# Patterns indicating a dictionary sheet by name
DICTIONARY_NAME_PATTERNS = [
    "legend",
    "dictionary",
    "symbols",
    "codes",
    "key",
    "glossary",
    "凡例",  # Japanese for "legend"
]

# Configuration for structure-based detection
MIN_DATA_ROWS = 3  # Minimum rows of symbol-meaning pairs
MAX_SYMBOL_LENGTH = 10  # Maximum length for a symbol value
MIN_MEANING_LENGTH = 5  # Minimum length for a meaning description
SAMPLE_ROWS = 20  # Number of rows to sample for structure analysis


class SymbolDetector:
    """Detects and extracts symbol dictionaries from Excel workbooks.

    This class analyzes Excel sheets to identify those that function as
    symbol dictionaries (legend sheets, code tables, etc.) using both
    name-based and structure-based detection methods.
    """

    def __init__(self):
        """Initialize the SymbolDetector."""
        pass

    def detect_dictionaries(
        self, workbook: Workbook
    ) -> tuple[list[DetectedDictionary], SymbolDetectionSummary]:
        """Detect all dictionary sheets in a workbook and extract their symbols.

        Args:
            workbook: openpyxl Workbook to analyze

        Returns:
            Tuple of (list of DetectedDictionary, SymbolDetectionSummary)
        """
        if not workbook or not workbook.worksheets:
            logger.warning("detect_dictionaries_called_with_empty_workbook")
            return [], SymbolDetectionSummary(
                dictionaries_found=0,
                total_symbols=0,
                detected_sheets=[],
                warnings=["Empty workbook provided"],
            )

        detected_dictionaries: list[DetectedDictionary] = []
        warnings: list[str] = []

        for sheet in workbook.worksheets:
            logger.debug(
                "analyzing_sheet_for_dictionary",
                sheet_name=sheet.title,
                max_row=sheet.max_row,
                max_col=sheet.max_column,
            )

            # Try name-based detection first (higher confidence)
            if self._is_dictionary_by_name(sheet.title):
                logger.info(
                    "dictionary_detected_by_name",
                    sheet_name=sheet.title,
                )
                symbols = self._extract_symbols(sheet)
                if symbols:
                    detected_dictionaries.append(
                        DetectedDictionary(
                            sheet_name=sheet.title,
                            detection_method="name",
                            confidence=0.9,
                            symbols=symbols,
                        )
                    )
                else:
                    warnings.append(
                        f"Sheet '{sheet.title}' matched dictionary name pattern "
                        "but no valid symbols were extracted"
                    )
                continue

            # Try structure-based detection
            is_dict, confidence = self._is_dictionary_by_structure(sheet)
            if is_dict:
                logger.info(
                    "dictionary_detected_by_structure",
                    sheet_name=sheet.title,
                    confidence=confidence,
                )
                symbols = self._extract_symbols(sheet)
                if symbols:
                    detected_dictionaries.append(
                        DetectedDictionary(
                            sheet_name=sheet.title,
                            detection_method="structure",
                            confidence=confidence,
                            symbols=symbols,
                        )
                    )
                else:
                    warnings.append(
                        f"Sheet '{sheet.title}' matched dictionary structure pattern "
                        "but no valid symbols were extracted"
                    )

        # Build summary
        total_symbols = sum(len(d.symbols) for d in detected_dictionaries)
        detected_sheets = [d.to_dict() for d in detected_dictionaries]

        summary = SymbolDetectionSummary(
            dictionaries_found=len(detected_dictionaries),
            total_symbols=total_symbols,
            detected_sheets=detected_sheets,
            warnings=warnings,
        )

        logger.info(
            "symbol_detection_completed",
            dictionaries_found=summary.dictionaries_found,
            total_symbols=summary.total_symbols,
            warnings_count=len(warnings),
        )

        return detected_dictionaries, summary

    def _is_dictionary_by_name(self, sheet_name: str) -> bool:
        """Check if a sheet name suggests it's a dictionary.

        Args:
            sheet_name: Name of the sheet to check

        Returns:
            True if the sheet name matches dictionary patterns
        """
        if not sheet_name:
            return False

        name_lower = sheet_name.lower()
        return any(pattern in name_lower for pattern in DICTIONARY_NAME_PATTERNS)

    def _is_dictionary_by_structure(
        self, sheet: Worksheet
    ) -> tuple[bool, float]:
        """Check if a sheet's structure suggests it's a dictionary.

        Analyzes the first SAMPLE_ROWS rows to determine if the sheet
        has a dictionary-like structure (short symbol column + longer meaning column).

        Args:
            sheet: Worksheet to analyze

        Returns:
            Tuple of (is_dictionary, confidence_score)
        """
        # Check for empty or single-row sheets
        if not sheet.max_row or sheet.max_row < MIN_DATA_ROWS:
            return False, 0.0

        # Check column count (dictionaries typically have 2-3 columns)
        if not sheet.max_column or sheet.max_column < 2 or sheet.max_column > 4:
            return False, 0.0

        # Check for merged cells (dictionaries rarely have complex merges)
        if sheet.merged_cells.ranges:
            # Allow small number of merges (e.g., header merges)
            if len(sheet.merged_cells.ranges) > 3:
                return False, 0.0

        # Sample rows and analyze structure
        short_count = 0  # Count of rows with short first column
        long_count = 0  # Count of rows with longer second column
        valid_rows = 0  # Total rows with data

        for row in sheet.iter_rows(min_row=1, max_row=min(SAMPLE_ROWS, sheet.max_row)):
            # Skip empty rows
            if all(cell.value is None for cell in row[:2]):
                continue

            valid_rows += 1

            # Get first two column values
            col1_val = self._get_cell_value(row[0]) if len(row) > 0 else ""
            col2_val = self._get_cell_value(row[1]) if len(row) > 1 else ""

            # Check pattern: short first column, longer second column
            if col1_val and 1 <= len(col1_val) <= MAX_SYMBOL_LENGTH:
                short_count += 1

            if col2_val and len(col2_val) >= MIN_MEANING_LENGTH:
                long_count += 1

        # Need minimum data rows and pattern match
        if valid_rows < MIN_DATA_ROWS:
            return False, 0.0

        # Calculate confidence based on pattern adherence
        short_ratio = short_count / valid_rows if valid_rows > 0 else 0
        long_ratio = long_count / valid_rows if valid_rows > 0 else 0

        # Both ratios should be reasonably high for a dictionary
        if short_ratio >= 0.5 and long_ratio >= 0.5:
            # Calculate combined confidence
            confidence = min(short_ratio, long_ratio) * 0.8  # Max 0.8 for structure detection
            return True, round(confidence, 2)

        return False, 0.0

    def _extract_symbols(self, sheet: Worksheet) -> list[SymbolEntry]:
        """Extract symbol-meaning pairs from a dictionary sheet.

        Args:
            sheet: Worksheet to extract symbols from

        Returns:
            List of SymbolEntry objects
        """
        symbols: list[SymbolEntry] = []
        seen_symbols: set[str] = set()  # Track duplicates

        if not sheet.max_row:
            return symbols

        # Determine if first row is a header (skip if so)
        first_row = list(sheet.iter_rows(min_row=1, max_row=1))[0]
        start_row = 1
        if self._is_header_row(first_row):
            start_row = 2
            logger.debug(
                "skipping_header_row",
                sheet_name=sheet.title,
            )

        # Extract symbols from data rows
        for row_idx, row in enumerate(
            sheet.iter_rows(min_row=start_row, max_row=sheet.max_row), start=start_row
        ):
            symbol_val = self._get_cell_value(row[0]) if len(row) > 0 else None
            meaning_val = self._get_cell_value(row[1]) if len(row) > 1 else None
            context_val = self._get_cell_value(row[2]) if len(row) > 2 else None

            # Validate symbol
            if not symbol_val or not meaning_val:
                continue

            # Check symbol length
            if len(symbol_val) > MAX_SYMBOL_LENGTH:
                logger.debug(
                    "skipping_long_symbol",
                    sheet_name=sheet.title,
                    row=row_idx,
                    symbol_length=len(symbol_val),
                )
                continue

            # Handle duplicates - keep first occurrence
            if symbol_val in seen_symbols:
                logger.debug(
                    "skipping_duplicate_symbol",
                    sheet_name=sheet.title,
                    row=row_idx,
                    symbol=symbol_val,
                )
                continue

            seen_symbols.add(symbol_val)

            try:
                entry = SymbolEntry(
                    symbol=symbol_val,
                    meaning=meaning_val,
                    context=context_val if context_val else None,
                )
                symbols.append(entry)
            except ValueError as e:
                logger.warning(
                    "invalid_symbol_entry",
                    sheet_name=sheet.title,
                    row=row_idx,
                    error=str(e),
                )

        logger.debug(
            "symbols_extracted",
            sheet_name=sheet.title,
            symbol_count=len(symbols),
            duplicates_skipped=len(seen_symbols) - len(symbols)
            if len(seen_symbols) > len(symbols)
            else 0,
        )

        return symbols

    def _get_cell_value(self, cell: Cell) -> Optional[str]:
        """Get string value from a cell, handling merged cells.

        Args:
            cell: Cell to get value from

        Returns:
            String value or None if empty
        """
        if isinstance(cell, MergedCell):
            return None

        if cell.value is None:
            return None

        value = str(cell.value).strip()
        return value if value else None

    def _is_header_row(self, row: tuple[Cell, ...]) -> bool:
        """Check if a row appears to be a header row.

        Args:
            row: Tuple of cells to check

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

        # Common header patterns
        header_patterns = [
            "symbol",
            "meaning",
            "description",
            "code",
            "value",
            "context",
            "definition",
            "記号",  # Japanese for "symbol"
            "意味",  # Japanese for "meaning"
        ]

        for cell in row[:3]:  # Check first 3 columns
            value = self._get_cell_value(cell)
            if value:
                value_lower = value.lower()
                if any(pattern in value_lower for pattern in header_patterns):
                    return True

        return False

    def detect_inline_legends(
        self, records: list[dict[str, Any]], sheet_name: str = "inline"
    ) -> list[SymbolEntry]:
        """Detect and extract inline legends from extracted records.

        Scans record content for patterns like "凡例）○：タグあり、－：タグなし"
        and extracts symbol-meaning pairs.

        Args:
            records: List of extracted record dictionaries with 'content' field
            sheet_name: Sheet name to use for context

        Returns:
            List of SymbolEntry objects extracted from inline legends
        """
        symbols: list[SymbolEntry] = []
        seen_symbols: set[str] = set()

        for record in records:
            content = record.get("content", {})
            if not content:
                continue

            # Scan all field values for legend patterns
            for field_name, value in content.items():
                if not value or not isinstance(value, str):
                    continue

                # Check for inline legend pattern
                match = INLINE_LEGEND_PATTERN.search(value)
                if match:
                    legend_text = match.group(1)
                    logger.info(
                        "inline_legend_detected",
                        field=field_name,
                        legend_text=legend_text[:50],
                    )

                    # Extract symbol-meaning pairs
                    for sym_match in SYMBOL_MEANING_PATTERN.finditer(legend_text):
                        symbol = sym_match.group(1).strip()
                        meaning = sym_match.group(2).strip()

                        if symbol in seen_symbols:
                            continue
                        seen_symbols.add(symbol)

                        try:
                            entry = SymbolEntry(
                                symbol=symbol,
                                meaning=meaning,
                                context=sheet_name,
                            )
                            symbols.append(entry)
                            logger.debug(
                                "inline_symbol_extracted",
                                symbol=symbol,
                                meaning=meaning,
                            )
                        except ValueError as e:
                            logger.warning(
                                "invalid_inline_symbol",
                                symbol=symbol,
                                error=str(e),
                            )

        logger.info(
            "inline_legend_detection_completed",
            symbols_found=len(symbols),
        )

        return symbols

    def store_symbols(
        self,
        detected_dictionaries: list[DetectedDictionary],
        document_id: UUID,
        session: Session,
    ) -> int:
        """Store extracted symbols in the database.

        Args:
            detected_dictionaries: List of detected dictionaries with symbols
            document_id: UUID of the source document
            session: SQLAlchemy database session

        Returns:
            Total number of symbols stored
        """
        if not detected_dictionaries:
            return 0

        total_stored = 0
        seen_symbols: set[str] = set()  # Track duplicates across sheets

        for dictionary in detected_dictionaries:
            records = []

            for symbol_entry in dictionary.symbols:
                # Skip duplicates (same symbol in same document)
                symbol_key = symbol_entry.symbol
                if symbol_key in seen_symbols:
                    logger.debug(
                        "skipping_duplicate_symbol_storage",
                        document_id=str(document_id),
                        symbol=symbol_entry.symbol,
                    )
                    continue

                seen_symbols.add(symbol_key)

                records.append(
                    {
                        "document_id": document_id,
                        "sheet_name": dictionary.sheet_name,
                        "symbol": symbol_entry.symbol,
                        "meaning": symbol_entry.meaning,
                        "context": symbol_entry.context,
                    }
                )

            if records:
                try:
                    session.bulk_insert_mappings(db_models.SymbolDictionary, records)
                    session.commit()
                    total_stored += len(records)

                    logger.info(
                        "symbols_stored",
                        document_id=str(document_id),
                        sheet_name=dictionary.sheet_name,
                        symbols_stored=len(records),
                    )

                except Exception as e:
                    session.rollback()
                    logger.error(
                        "symbol_storage_failed",
                        document_id=str(document_id),
                        sheet_name=dictionary.sheet_name,
                        error=str(e),
                    )
                    raise

        logger.info(
            "all_symbols_stored",
            document_id=str(document_id),
            total_stored=total_stored,
        )

        return total_stored