"""Direct XML extraction of cell colors from Excel files.

This module extracts cell background and text colors from Excel OOXML format (.xlsx/.xlsm)
without modifying the file. Colors are normalized to hex RGB format and filtered to exclude
default colors (white background + black text).

Pattern follows strikethrough_utils.py for OOXML XML parsing.
"""

import zipfile
from pathlib import Path
from typing import Dict, Optional, List
from dataclasses import dataclass, field
import structlog

try:
    from lxml import etree as ET
    HAS_LXML = True
except ImportError:
    import xml.etree.ElementTree as ET
    HAS_LXML = False

logger = structlog.get_logger(__name__)

# Excel OOXML namespaces
NAMESPACES = {
    'ss': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main',
    'r': 'http://schemas.openxmlformats.org/officeDocument/2006/relationships',
    'a': 'http://schemas.openxmlformats.org/drawingml/2006/main'
}

if not HAS_LXML:
    # Register namespaces to preserve prefixes
    for prefix, uri in NAMESPACES.items():
        ET.register_namespace(prefix, uri)


# Standard Excel indexed color palette (0-64)
INDEXED_COLORS = {
    0: "#000000", 1: "#FFFFFF", 2: "#FF0000", 3: "#00FF00",
    4: "#0000FF", 5: "#FFFF00", 6: "#FF00FF", 7: "#00FFFF",
    8: "#000000", 9: "#FFFFFF", 10: "#FF0000", 11: "#00FF00",
    12: "#0000FF", 13: "#FFFF00", 14: "#FF00FF", 15: "#00FFFF",
    16: "#800000", 17: "#008000", 18: "#000080", 19: "#808000",
    20: "#800080", 21: "#008080", 22: "#C0C0C0", 23: "#808080",
    24: "#9999FF", 25: "#993366", 26: "#FFFFCC", 27: "#CCFFFF",
    28: "#660066", 29: "#FF8080", 30: "#0066CC", 31: "#CCCCFF",
    32: "#000080", 33: "#FF00FF", 34: "#FFFF00", 35: "#00FFFF",
    36: "#800080", 37: "#800000", 38: "#008080", 39: "#0000FF",
    40: "#00CCFF", 41: "#CCFFFF", 42: "#CCFFCC", 43: "#FFFF99",
    44: "#99CCFF", 45: "#FF99CC", 46: "#CC99FF", 47: "#FFCC99",
    48: "#3366FF", 49: "#33CCCC", 50: "#99CC00", 51: "#FFCC00",
    52: "#FF9900", 53: "#FF6600", 54: "#666699", 55: "#969696",
    56: "#003366", 57: "#339966", 58: "#003300", 59: "#333300",
    60: "#993300", 61: "#993366", 62: "#333399", 63: "#333333",
    64: "#000000"  # automatic = black
}

# Default theme colors (Office 2007-2019 standard theme)
# These are fallback values if theme1.xml is missing
DEFAULT_THEME_COLORS = {
    0: "#FFFFFF",  # lt1 (Light 1 - Background 1)
    1: "#000000",  # dk1 (Dark 1 - Text 1)
    2: "#EEECE1",  # lt2 (Light 2 - Background 2)
    3: "#1F497D",  # dk2 (Dark 2 - Text 2)
    4: "#4F81BD",  # accent1
    5: "#C0504D",  # accent2
    6: "#9BBB59",  # accent3
    7: "#8064A2",  # accent4
    8: "#4BACC6",  # accent5
    9: "#F79646",  # accent6
    10: "#0000FF", # hlink (Hyperlink)
    11: "#800080"  # folHlink (Followed Hyperlink)
}


@dataclass
class ColorExtractionResult:
    """Result of color extraction operation."""
    color_map: Dict[str, Dict[str, Dict[str, str]]]  # {sheet_name: {"row,col": {bg_color, font_color}}}
    status: str  # "success" | "no_colors" | "partial_failure" | "failed"
    cells_processed: int
    cells_with_colors: int
    error_message: Optional[str] = None
    warnings: List[str] = field(default_factory=list)


class CellColorExtractor:
    """Extracts cell colors from Excel files via direct OOXML XML parsing."""

    def __init__(self):
        """Initialize color extractor."""
        self.theme_colors: Dict[int, str] = DEFAULT_THEME_COLORS.copy()

    def extract_colors(self, file_path: str) -> ColorExtractionResult:
        """
        Extract cell colors from Excel file.

        Args:
            file_path: Path to Excel file (.xlsx or .xlsm)

        Returns:
            ColorExtractionResult with color map and status info
        """
        file_path = Path(file_path)
        warnings = []
        cells_processed = 0
        cells_with_colors = 0

        try:
            # Validate file is OOXML Excel
            with zipfile.ZipFile(file_path, 'r') as zip_ref:
                namelist = zip_ref.namelist()
                if not ('[Content_Types].xml' in namelist and any('xl/workbook.xml' in f for f in namelist)):
                    return ColorExtractionResult(
                        color_map={},
                        status="failed",
                        cells_processed=0,
                        cells_with_colors=0,
                        error_message="Not a valid Excel file"
                    )

        except FileNotFoundError:
            return ColorExtractionResult(
                color_map={},
                status="failed",
                cells_processed=0,
                cells_with_colors=0,
                error_message=f"File not found: {file_path}"
            )
        except zipfile.BadZipFile:
            return ColorExtractionResult(
                color_map={},
                status="failed",
                cells_processed=0,
                cells_with_colors=0,
                error_message="Password-protected or corrupted file"
            )

        # Extract and process
        try:
            with zipfile.ZipFile(file_path, 'r') as zip_ref:
                # File size check - count sheets and estimate cells
                sheet_count = len([f for f in zip_ref.namelist() if f.startswith('xl/worksheets/sheet') and f.endswith('.xml')])

                if sheet_count > 20:
                    logger.warning(f"File has {sheet_count} sheets (>20), skipping color extraction")
                    return ColorExtractionResult(
                        color_map={},
                        status="failed",
                        cells_processed=0,
                        cells_with_colors=0,
                        error_message=f"File has {sheet_count} sheets, exceeds limit of 20"
                    )

                # Extract theme colors if available
                if 'xl/theme/theme1.xml' in zip_ref.namelist():
                    try:
                        with zip_ref.open('xl/theme/theme1.xml') as theme_file:
                            self._parse_theme_colors(theme_file.read())
                    except Exception as e:
                        warnings.append(f"Failed to parse theme colors: {e}")
                        logger.warning(f"Failed to parse theme colors, using defaults: {e}")

                # Parse styles.xml to build color maps
                if 'xl/styles.xml' not in zip_ref.namelist():
                    return ColorExtractionResult(
                        color_map={},
                        status="failed",
                        cells_processed=0,
                        cells_with_colors=0,
                        error_message="Missing styles.xml"
                    )

                with zip_ref.open('xl/styles.xml') as styles_file:
                    style_colors = self._parse_styles(styles_file.read(), warnings)

                # Get sheet names mapping
                sheet_names = self._parse_sheet_names(zip_ref)

                # Extract colors from each worksheet
                color_map = {}
                for sheet_index, sheet_name in sheet_names.items():
                    sheet_file = f'xl/worksheets/sheet{sheet_index}.xml'
                    if sheet_file in zip_ref.namelist():
                        with zip_ref.open(sheet_file) as ws_file:
                            sheet_colors, sheet_cells = self._extract_sheet_colors(
                                ws_file.read(),
                                style_colors,
                                warnings
                            )
                            cells_processed += sheet_cells
                            if sheet_colors:
                                color_map[sheet_name] = sheet_colors
                                cells_with_colors += len(sheet_colors)

            # Determine status
            if cells_with_colors == 0:
                status = "no_colors"
            elif warnings:
                status = "partial_failure"
            else:
                status = "success"

            return ColorExtractionResult(
                color_map=color_map,
                status=status,
                cells_processed=cells_processed,
                cells_with_colors=cells_with_colors,
                warnings=warnings
            )

        except Exception as e:
            logger.error(f"Color extraction failed: {e}")
            return ColorExtractionResult(
                color_map={},
                status="failed",
                cells_processed=cells_processed,
                cells_with_colors=cells_with_colors,
                error_message=str(e),
                warnings=warnings
            )

    def _parse_theme_colors(self, theme_xml: bytes) -> None:
        """Parse theme1.xml to extract base theme colors."""
        try:
            root = ET.fromstring(theme_xml)

            # Theme colors are in a:clrScheme
            color_scheme = root.find('.//a:clrScheme', NAMESPACES)
            if color_scheme is not None:
                # Map of theme color names to indices
                theme_map = {
                    'a:lt1': 0, 'a:dk1': 1, 'a:lt2': 2, 'a:dk2': 3,
                    'a:accent1': 4, 'a:accent2': 5, 'a:accent3': 6,
                    'a:accent4': 7, 'a:accent5': 8, 'a:accent6': 9,
                    'a:hlink': 10, 'a:folHlink': 11
                }

                for color_name, index in theme_map.items():
                    color_elem = color_scheme.find(color_name, NAMESPACES)
                    if color_elem is not None:
                        # Try to find srgbClr or sysClr
                        srgb = color_elem.find('.//a:srgbClr', NAMESPACES)
                        if srgb is not None:
                            rgb_val = srgb.get('val')
                            if rgb_val:
                                self.theme_colors[index] = f"#{rgb_val.upper()}"
                        else:
                            sys_clr = color_elem.find('.//a:sysClr', NAMESPACES)
                            if sys_clr is not None:
                                last_clr = sys_clr.get('lastClr')
                                if last_clr:
                                    self.theme_colors[index] = f"#{last_clr.upper()}"

        except Exception as e:
            logger.warning(f"Failed to parse theme colors: {e}")

    def _parse_styles(self, styles_xml: bytes, warnings: List[str]) -> Dict[int, Dict[str, Optional[str]]]:
        """
        Parse styles.xml to build style ID → colors mapping.

        Returns:
            Dict mapping style_id to {bg_color, font_color}
        """
        root = ET.fromstring(styles_xml)
        style_colors = {}

        # Step 1: Build font ID → font color map
        font_colors = {}
        fonts = root.find('.//ss:fonts', NAMESPACES)
        if fonts is not None:
            for font_id, font in enumerate(fonts.findall('ss:font', NAMESPACES)):
                color_elem = font.find('ss:color', NAMESPACES)
                if color_elem is not None:
                    font_colors[font_id] = self._parse_color_element(color_elem, warnings)

        # Step 2: Build fill ID → background color map
        fill_colors = {}
        fills = root.find('.//ss:fills', NAMESPACES)
        if fills is not None:
            for fill_id, fill in enumerate(fills.findall('ss:fill', NAMESPACES)):
                pattern_fill = fill.find('ss:patternFill', NAMESPACES)
                if pattern_fill is not None:
                    pattern_type = pattern_fill.get('patternType')
                    # Only process solid fills
                    if pattern_type == 'solid':
                        fg_color = pattern_fill.find('ss:fgColor', NAMESPACES)
                        if fg_color is not None:
                            fill_colors[fill_id] = self._parse_color_element(fg_color, warnings)

        # Step 3: Build style ID → (font color, bg color) map
        cell_xfs = root.find('.//ss:cellXfs', NAMESPACES)
        if cell_xfs is not None:
            for style_id, xf in enumerate(cell_xfs.findall('ss:xf', NAMESPACES)):
                font_id = xf.get('fontId')
                fill_id = xf.get('fillId')

                font_color = None
                bg_color = None

                if font_id is not None:
                    try:
                        font_color = font_colors.get(int(font_id))
                    except (ValueError, KeyError):
                        pass

                if fill_id is not None:
                    try:
                        bg_color = fill_colors.get(int(fill_id))
                    except (ValueError, KeyError):
                        pass

                # Only add if has non-default colors
                if not self._is_default_color(bg_color, font_color):
                    style_colors[style_id] = {
                        'bg_color': bg_color,
                        'font_color': font_color
                    }

        return style_colors

    def _parse_color_element(self, color_elem, warnings: List[str]) -> Optional[str]:
        """Parse a <color> element and return hex RGB."""
        # RGB color
        rgb = color_elem.get('rgb')
        if rgb:
            return self._rgb_to_hex(rgb)

        # Theme color
        theme = color_elem.get('theme')
        if theme is not None:
            try:
                theme_index = int(theme)
                tint = color_elem.get('tint')
                tint_val = float(tint) if tint else 0.0
                return self._theme_color_to_hex(theme_index, tint_val)
            except (ValueError, KeyError) as e:
                warnings.append(f"Invalid theme color: {theme}")
                return None

        # Indexed color
        indexed = color_elem.get('indexed')
        if indexed is not None:
            try:
                return self._indexed_color_to_hex(int(indexed))
            except (ValueError, KeyError):
                warnings.append(f"Invalid indexed color: {indexed}")
                return None

        return None

    def _rgb_to_hex(self, rgb: str) -> str:
        """Convert RGB string to hex format."""
        # Input: "RRGGBB" or "AARRGGBB" (ARGB format)
        if len(rgb) == 8:  # ARGB: strip alpha
            rgb = rgb[2:]
        return f"#{rgb.upper()}"

    def _theme_color_to_hex(self, theme_index: int, tint: float = 0.0) -> str:
        """Convert theme color with tint to hex RGB."""
        base_hex = self.theme_colors.get(theme_index, "#000000")

        if tint == 0:
            return base_hex

        # Apply tint using HSL color space
        r = int(base_hex[1:3], 16)
        g = int(base_hex[3:5], 16)
        b = int(base_hex[5:7], 16)

        h, l, s = self._rgb_to_hsl(r / 255, g / 255, b / 255)

        # Apply tint to luminance
        if tint > 0:
            l = l * (1 - tint) + tint
        else:
            l = l * (1 + tint)

        r, g, b = self._hsl_to_rgb(h, l, s)

        return f"#{int(r*255):02X}{int(g*255):02X}{int(b*255):02X}"

    def _indexed_color_to_hex(self, index: int) -> str:
        """Convert indexed palette color to hex RGB."""
        return INDEXED_COLORS.get(index, "#000000")

    def _rgb_to_hsl(self, r: float, g: float, b: float) -> tuple:
        """Convert RGB (0-1 range) to HSL. Returns (h, l, s) with h in 0-360, l/s in 0-1."""
        max_c = max(r, g, b)
        min_c = min(r, g, b)
        l = (max_c + min_c) / 2

        if max_c == min_c:
            h = s = 0.0
        else:
            d = max_c - min_c
            s = d / (2 - max_c - min_c) if l > 0.5 else d / (max_c + min_c)

            if max_c == r:
                h = (g - b) / d + (6 if g < b else 0)
            elif max_c == g:
                h = (b - r) / d + 2
            else:
                h = (r - g) / d + 4
            h /= 6

        return (h * 360, l, s)

    def _hsl_to_rgb(self, h: float, l: float, s: float) -> tuple:
        """Convert HSL to RGB. h in 0-360, l/s in 0-1. Returns (r, g, b) in 0-1 range."""
        h = h / 360

        if s == 0:
            return (l, l, l)

        def hue_to_rgb(p, q, t):
            if t < 0:
                t += 1
            if t > 1:
                t -= 1
            if t < 1/6:
                return p + (q - p) * 6 * t
            if t < 1/2:
                return q
            if t < 2/3:
                return p + (q - p) * (2/3 - t) * 6
            return p

        q = l * (1 + s) if l < 0.5 else l + s - l * s
        p = 2 * l - q

        return (
            hue_to_rgb(p, q, h + 1/3),
            hue_to_rgb(p, q, h),
            hue_to_rgb(p, q, h - 1/3)
        )

    def _is_default_color(self, bg_color: Optional[str], font_color: Optional[str]) -> bool:
        """Check if colors are default (white bg + black text)."""
        default_backgrounds = {"#FFFFFF", None}
        default_fonts = {"#000000", None}
        return bg_color in default_backgrounds and font_color in default_fonts

    def _parse_sheet_names(self, zip_ref: zipfile.ZipFile) -> Dict[int, str]:
        """Parse workbook.xml and workbook.xml.rels to get sheet names."""
        sheet_names = {}

        try:
            # Parse workbook.xml to get sheet names and r:id references
            with zip_ref.open('xl/workbook.xml') as wb_file:
                root = ET.fromstring(wb_file.read())
                sheets = root.find('.//ss:sheets', NAMESPACES)

                if sheets is not None:
                    for idx, sheet in enumerate(sheets.findall('ss:sheet', NAMESPACES), start=1):
                        name = sheet.get('name')
                        if name:
                            sheet_names[idx] = name

        except Exception as e:
            logger.warning(f"Failed to parse sheet names: {e}")
            # Fallback to generic names
            sheet_count = len([f for f in zip_ref.namelist() if f.startswith('xl/worksheets/sheet')])
            sheet_names = {i: f"Sheet{i}" for i in range(1, sheet_count + 1)}

        return sheet_names

    def _extract_sheet_colors(
        self,
        sheet_xml: bytes,
        style_colors: Dict[int, Dict[str, Optional[str]]],
        warnings: List[str]
    ) -> tuple:
        """
        Extract colors from a worksheet.

        Returns:
            Tuple of (color_map, cells_processed)
        """
        root = ET.fromstring(sheet_xml)
        sheet_colors = {}
        cells_processed = 0

        # Parse merged cells for expansion
        merge_map = self._parse_merged_cells(root)

        # Find all cells in worksheet
        for row in root.findall('.//ss:row', NAMESPACES):
            row_num = row.get('r')
            if row_num:
                row_idx = int(row_num) - 1  # Convert to 0-indexed
            else:
                continue

            for cell in row.findall('ss:c', NAMESPACES):
                cells_processed += 1

                # Get cell reference (e.g., "A1")
                cell_ref = cell.get('r')
                if not cell_ref:
                    continue

                # Extract column from cell reference
                col_letter = ''.join(filter(str.isalpha, cell_ref))
                col_idx = self._col_letter_to_index(col_letter)

                # Check if cell has a style
                style_id = cell.get('s')
                if style_id is not None:
                    try:
                        style_id_int = int(style_id)
                        if style_id_int in style_colors:
                            colors = style_colors[style_id_int]
                            key = f"{row_idx},{col_idx}"
                            sheet_colors[key] = colors
                    except (ValueError, KeyError):
                        pass

        # Expand merged cell colors
        for top_left_key, merged_keys in merge_map.items():
            if top_left_key in sheet_colors:
                top_left_color = sheet_colors[top_left_key]
                for key in merged_keys:
                    sheet_colors[key] = dict(top_left_color)  # Copy, not reference

        return sheet_colors, cells_processed

    def _parse_merged_cells(self, root) -> Dict[str, List[str]]:
        """Parse merged cells and return map of top-left to constituent cells."""
        merge_map = {}

        merge_cells = root.find('.//ss:mergeCells', NAMESPACES)
        if merge_cells is not None:
            for merge_cell in merge_cells.findall('ss:mergeCell', NAMESPACES):
                ref = merge_cell.get('ref')
                if ref and ':' in ref:
                    # Parse range like "A1:C3"
                    start_ref, end_ref = ref.split(':')

                    start_col = ''.join(filter(str.isalpha, start_ref))
                    start_row = int(''.join(filter(str.isdigit, start_ref))) - 1

                    end_col = ''.join(filter(str.isalpha, end_ref))
                    end_row = int(''.join(filter(str.isdigit, end_ref))) - 1

                    start_col_idx = self._col_letter_to_index(start_col)
                    end_col_idx = self._col_letter_to_index(end_col)

                    top_left_key = f"{start_row},{start_col_idx}"
                    merged_keys = []

                    for row in range(start_row, end_row + 1):
                        for col in range(start_col_idx, end_col_idx + 1):
                            key = f"{row},{col}"
                            if key != top_left_key:
                                merged_keys.append(key)

                    if merged_keys:
                        merge_map[top_left_key] = merged_keys

        return merge_map

    def _col_letter_to_index(self, col_letter: str) -> int:
        """Convert column letter (A, B, AA, etc.) to 0-indexed column number."""
        col_idx = 0
        for char in col_letter:
            col_idx = col_idx * 26 + (ord(char.upper()) - ord('A') + 1)
        return col_idx - 1
