"""
Smart column mapping system for CSV/Excel imports with auto-detection.
Handles flexible column naming and provides intelligent suggestions.
"""

from typing import Dict, List, Tuple, Optional
from difflib import SequenceMatcher
import re


class ColumnMapper:
    """
    Intelligent column mapper that handles flexible naming conventions
    and provides best-match suggestions for database field mapping.
    """

    # Database field definitions with aliases and patterns
    CUSTOMER_FIELDS = {
        "name": {
            "aliases": ["customer_name", "customername", "customer", "full_name", "fullname"],
            "patterns": [r"^name$", r"customer.*name", r"^customer$"],
            "priority": 100,
            "required": True,
        },
        "email": {
            "aliases": ["e_mail", "emailaddress", "email_address", "e_mail_address"],
            "patterns": [r"email", r"e.?mail"],
            "priority": 90,
            "required": False,
        },
        "phone": {
            "aliases": ["phone_number", "phonenumber", "tel", "telephone", "mobile", "cell"],
            "patterns": [r"phone", r"tel", r"mobile", r"cell"],
            "priority": 85,
            "required": False,
        },
        "address": {
            "aliases": ["street_address", "physical_address", "location", "streetaddress"],
            "patterns": [r"address", r"street"],
            "priority": 80,
            "required": False,
        },
        "contact_person": {
            "aliases": ["contact", "contactperson", "contact_name", "person", "company_name", "companyname", "company name"],
            "patterns": [r"contact.*person", r"contact.*name", r"company.*name"],
            "priority": 75,
            "required": False,
        },
        "vat_reg_number": {
            "aliases": ["vat_number", "vatnumber", "vat", "tax_number", "pin", "tin"],
            "patterns": [r"vat", r"tax.*number", r"pin", r"tin"],
            "priority": 70,
            "required": False,
        },
        "customer_type": {
            "aliases": ["type", "customer_category", "category"],
            "patterns": [r"type", r"category", r"customer.*type"],
            "priority": 65,
            "required": False,
        },
        "credit_limit": {
            "aliases": ["creditlimit", "max_credit", "maxcredit"],
            "patterns": [r"credit.*limit"],
            "priority": 60,
            "required": False,
        },
        "payment_terms": {
            "aliases": ["terms", "paymentterms", "payment_term", "payment days"],
            "patterns": [r"payment.*term", r"terms"],
            "priority": 55,
            "required": False,
        },
        "opening_balance": {
            "aliases": ["open_balance", "openingbalance", "initial_balance", "opening", "balance"],
            "patterns": [r"open.*balance", r"^balance$", r"^opening$"],
            "priority": 50,
            "required": False,
        },
        "notes": {
            "aliases": ["note", "remarks", "comments", "description"],
            "patterns": [r"notes", r"remarks", r"comments"],
            "priority": 40,
            "required": False,
        },
        "active": {
            "aliases": ["is_active", "isactive", "enabled", "disabled"],
            "patterns": [r"^active$", r"^is_active$"],
            "priority": 35,
            "required": False,
        },
        "tax_exempt": {
            "aliases": ["taxexempt", "exempt", "tax_exempt_status"],
            "patterns": [r"tax.*exempt", r"exempt"],
            "priority": 30,
            "required": False,
        },
    }

    @staticmethod
    def normalize_column_name(col_name: str) -> str:
        """Normalize column name for comparison."""
        return re.sub(r"[^a-z0-9]", "", col_name.lower())

    @classmethod
    def get_similarity_score(cls, text1: str, text2: str) -> float:
        """Calculate string similarity score (0.0 to 1.0)."""
        normalized1 = cls.normalize_column_name(text1)
        normalized2 = cls.normalize_column_name(text2)
        return SequenceMatcher(None, normalized1, normalized2).ratio()

    @classmethod
    def detect_field_for_column(
        cls, column_name: str, field_config: Dict
    ) -> float:
        """
        Calculate match score for a column against a database field.
        Returns score from 0.0 to 1.0.
        """
        normalized = cls.normalize_column_name(column_name)

        # Check exact match with aliases
        for alias in field_config.get("aliases", []):
            if normalized == cls.normalize_column_name(alias):
                return 1.0

        # Check pattern matches
        for pattern in field_config.get("patterns", []):
            if re.search(pattern, normalized):
                return 0.95

        # Check similarity with aliases
        max_similarity = 0.0
        for alias in field_config.get("aliases", []):
            similarity = cls.get_similarity_score(column_name, alias)
            max_similarity = max(max_similarity, similarity)

        return max_similarity

    @classmethod
    def auto_detect_mappings(
        cls, file_columns: List[str], threshold: float = 0.6
    ) -> Dict[str, Optional[str]]:
        """
        Auto-detect best mapping from file columns to database fields.

        Args:
            file_columns: List of column names from uploaded file
            threshold: Minimum similarity score to suggest a mapping

        Returns:
            Dictionary mapping file columns -> database field names
            None values indicate no good match found
        """
        mapping = {}
        used_fields = set()  # Track which DB fields are already mapped

        # Sort columns by best match across all fields
        scored_columns = []

        for col in file_columns:
            best_field = None
            best_score = 0.0

            for db_field, config in cls.CUSTOMER_FIELDS.items():
                if db_field in used_fields:
                    continue  # Skip already mapped fields

                score = cls.detect_field_for_column(col, config)
                if score > best_score:
                    best_score = score
                    best_field = db_field

            scored_columns.append((col, best_field, best_score))

        # Sort by priority of matched fields (if matched)
        scored_columns.sort(
            key=lambda x: (x[1] is None, -(cls.CUSTOMER_FIELDS.get(x[1], {}).get("priority", 0)))
            if x[1] else (False, -x[2])
        )

        # Assign mappings
        for col, field, score in scored_columns:
            if field and field not in used_fields and score >= threshold:
                mapping[col] = field
                used_fields.add(field)
            else:
                mapping[col] = None

        return mapping

    @classmethod
    def get_all_possible_fields(cls) -> Dict[str, Dict]:
        """Get all database fields with their metadata."""
        return cls.CUSTOMER_FIELDS

    @classmethod
    def get_field_metadata(cls, field_name: str) -> Optional[Dict]:
        """Get metadata for a specific field."""
        return cls.CUSTOMER_FIELDS.get(field_name)

    @classmethod
    def validate_mapping(cls, mapping: Dict[str, str]) -> Tuple[bool, List[str]]:
        """
        Validate a mapping to ensure all required fields are present.

        Returns:
            (is_valid, list_of_errors)
        """
        errors = []

        # Check for required fields
        for field_name, config in cls.CUSTOMER_FIELDS.items():
            if config.get("required", False):
                # Check if this field is in the mapping values
                if field_name not in mapping.values():
                    errors.append(f"Missing required field: {field_name}")

        # Check for duplicate mappings
        mapped_values = [v for v in mapping.values() if v is not None]
        if len(mapped_values) != len(set(mapped_values)):
            errors.append("Duplicate mappings detected (multiple columns mapped to same field)")

        return len(errors) == 0, errors


class ColumnPreview:
    """Handles preview of data with suggested mappings."""

    @staticmethod
    def generate_preview(
        file_columns: List[str],
        file_data: List[Dict],
        mappings: Dict[str, Optional[str]],
        preview_rows: int = 5,
    ) -> Dict:
        """
        Generate a preview of how data will be imported with suggested mappings.

        Args:
            file_columns: Columns from uploaded file
            file_data: Sample data rows
            mappings: {file_col -> db_field} mapping
            preview_rows: Number of rows to show in preview

        Returns:
            Preview data with statistics
        """
        preview_data = []

        for idx, row in enumerate(file_data[:preview_rows]):
            preview_row = {}
            for file_col in file_columns:
                db_field = mappings.get(file_col)
                preview_row[db_field or f"[unmapped] {file_col}"] = str(row.get(file_col, ""))
            preview_data.append(preview_row)

        mapped_count = sum(1 for v in mappings.values() if v is not None)
        unmapped_count = sum(1 for v in mappings.values() if v is None)

        return {
            "total_columns": len(file_columns),
            "mapped_columns": mapped_count,
            "unmapped_columns": unmapped_count,
            "total_rows": len(file_data),
            "preview_rows": preview_data,
            "mapping_summary": mappings,
        }
