"""Direct XML manipulation for removing strikethrough text from Excel files.

This module works directly with the Excel XML format (OOXML) to remove
strikethrough text without relying on openpyxl's serialization, which
can cause corruption issues with certain Excel files.

Note: Only works with .xlsx/.xlsm files. Use excel_conversion_utils.convert_xls_to_xlsx()
first if you have .xls files.
"""

import zipfile
from pathlib import Path
import tempfile
from typing import Optional
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'
}

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


def remove_strikethrough_cells(input_path: str, output_path: Optional[str] = None) -> dict:
    """
    Remove strikethrough text from Excel file by directly manipulating XML.

    This function works directly with Excel's OOXML format (.xlsx) to avoid openpyxl
    serialization issues that can cause file corruption.

    Note: Only works with .xlsx/.xlsm files. If you have .xls files, use
    convert_xls_to_xlsx() first to convert them.

    Args:
        input_path: Path to input Excel file (.xlsx or .xlsm)
        output_path: Optional path for output file. If None, overwrites input.

    Returns:
        Statistics dict with:
        - cells_modified: Number of cells with strikethrough text removed
        - sheets_affected: Number of sheets modified
        - runs_removed: Total number of rich text runs removed
        - output_file: Path to the output file

    Raises:
        ValueError: If file is not a valid .xlsx/.xlsm file

    Example:
        # For .xlsx files
        result = remove_strikethrough_cells('file.xlsx', 'file_cleaned.xlsx')
        print(f"Removed {result['runs_removed']} strikethrough runs")

        # For .xls files, convert first
        from src.extraction_v2.excel_conversion_utils import convert_xls_to_xlsx
        xlsx_path, converted = convert_xls_to_xlsx('file.xls')
        result = remove_strikethrough_cells(xlsx_path, 'file_cleaned.xlsx')
    """
    if output_path is None:
        output_path = input_path

    input_path = Path(input_path)
    output_path = Path(output_path)

    # Validate that file is .xlsx format (OOXML)
    try:
        with zipfile.ZipFile(input_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)):
                raise ValueError(
                    f"File {input_path.name} is not a valid .xlsx/.xlsm file. "
                    "Please use convert_xls_to_xlsx() first if you have .xls files."
                )
    except zipfile.BadZipFile:
        raise ValueError(
            f"File {input_path.name} is not a valid .xlsx/.xlsm file (not a ZIP archive). "
            "Please use convert_xls_to_xlsx() first if you have .xls files."
        )

    # Process the file
    # Create temporary directory for extraction
    with tempfile.TemporaryDirectory() as temp_dir:
        temp_path = Path(temp_dir)

        # Extract Excel file (it's a ZIP archive)
        logger.debug(f"Extracting Excel file to {temp_path}")
        with zipfile.ZipFile(input_path, 'r') as zip_ref:
            zip_ref.extractall(temp_path)

            # Process shared strings XML (where rich text formatting is stored)
            shared_strings_file = temp_path / 'xl' / 'sharedStrings.xml'
            stats = {
                'cells_modified': 0,
                'sheets_affected': 0,
                'runs_removed': 0,
                'styles_modified': 0
            }

            if shared_strings_file.exists():
                logger.debug(f"Processing sharedStrings.xml")
                result = _process_shared_strings_xml(shared_strings_file)
                stats['cells_modified'] = result['cells_modified']
                stats['runs_removed'] = result['runs_removed']

                if result['cells_modified'] > 0:
                    logger.info(
                        f"sharedStrings.xml: Removed {result['runs_removed']} "
                        f"strikethrough runs from {result['cells_modified']} strings"
                    )

            # Process styles XML (where cell-level formatting is stored)
            # First, identify which style indices have strikethrough
            styles_file = temp_path / 'xl' / 'styles.xml'
            strikethrough_style_ids = set()

            if styles_file.exists():
                logger.debug(f"Processing styles.xml to identify strikethrough styles")
                strikethrough_style_ids = _identify_strikethrough_styles(styles_file)

                if strikethrough_style_ids:
                    logger.info(
                        f"styles.xml: Found {len(strikethrough_style_ids)} styles with strikethrough"
                    )

            # Process worksheet XMLs to clear cells with strikethrough styles
            worksheets_dir = temp_path / 'xl' / 'worksheets'
            cells_cleared = 0

            if worksheets_dir.exists() and strikethrough_style_ids:
                for sheet_file in worksheets_dir.glob('sheet*.xml'):
                    logger.debug(f"Processing {sheet_file.name}")
                    result = _clear_cells_with_strikethrough_style(sheet_file, strikethrough_style_ids)
                    cells_cleared += result['cells_cleared']

                    if result['cells_cleared'] > 0:
                        stats['sheets_affected'] += 1

                if cells_cleared > 0:
                    logger.info(
                        f"Worksheets: Cleared {cells_cleared} cells with strikethrough formatting"
                    )

            stats['cells_modified'] += cells_cleared

            # Repackage as ZIP (Excel file)
            logger.debug(f"Repackaging to {output_path}")
            _create_xlsx_from_directory(temp_path, output_path)

    logger.info(
        f"Strikethrough removal complete: {stats['runs_removed']} runs removed "
        f"from {stats['cells_modified']} cells across {stats['sheets_affected']} sheets"
    )

    # Add output file path to stats
    stats['output_file'] = str(output_path)

    return stats


def _process_shared_strings_xml(strings_path: Path) -> dict:
    """
    Process sharedStrings.xml to remove strikethrough text runs.

    In Excel's OOXML format, cell text content (especially rich text) is stored
    in a shared strings table to optimize file size. Each unique text string is
    stored once and cells reference it by index.

    Args:
        strings_path: Path to sharedStrings.xml file

    Returns:
        Dict with cells_modified and runs_removed counts
    """
    # Parse XML
    tree = ET.parse(strings_path)
    root = tree.getroot()

    strings_modified = 0
    runs_removed = 0

    # Find all string items (si elements)
    # Each <si> represents a unique string in the workbook
    for si in root.findall('.//ss:si', NAMESPACES):
        # Check if this string has rich text runs <r>
        modified = False
        runs_to_remove = []

        # Find all text runs in this string
        for run in si.findall('ss:r', NAMESPACES):
            # Check for strikethrough formatting in run properties
            rpr = run.find('ss:rPr', NAMESPACES)
            if rpr is not None:
                strike = rpr.find('ss:strike', NAMESPACES)
                if strike is not None:
                    # This run has strikethrough - mark for removal
                    runs_to_remove.append(run)
                    modified = True

        # Remove strikethrough runs
        if modified:
            for run in runs_to_remove:
                si.remove(run)
                runs_removed += 1

            # CRITICAL: Remove phonetic properties (rPh and phoneticPr)
            # These contain byte offsets that become invalid after removing text
            # Excel can't handle invalid byte positions and will show repair errors
            for rph in si.findall('ss:rPh', NAMESPACES):
                si.remove(rph)

            phonetic_pr = si.find('ss:phoneticPr', NAMESPACES)
            if phonetic_pr is not None:
                si.remove(phonetic_pr)

            # If no runs left after removal, check if we need to handle it
            remaining_runs = si.findall('ss:r', NAMESPACES)
            plain_text = si.find('ss:t', NAMESPACES)

            if len(remaining_runs) == 0 and plain_text is None:
                # All content was strikethrough, replace with empty string
                empty_text = ET.Element('{%s}t' % NAMESPACES['ss'])
                empty_text.text = ''
                si.append(empty_text)

            strings_modified += 1

    # Write modified XML back to file
    if HAS_LXML:
        # lxml preserves formatting better
        tree.write(
            str(strings_path),
            encoding='utf-8',
            xml_declaration=True,
            pretty_print=False  # Don't reformat, keep original structure
        )
    else:
        # Standard library ET
        tree.write(strings_path, encoding='utf-8', xml_declaration=True)

    logger.debug(
        f"Processed sharedStrings.xml: {strings_modified} strings modified, "
        f"{runs_removed} runs removed"
    )

    return {
        'cells_modified': strings_modified,
        'runs_removed': runs_removed
    }


def _identify_strikethrough_styles(styles_path: Path) -> set:
    """
    Identify cell style IDs that have strikethrough formatting.

    Excel's cell formatting works by:
    1. Fonts are defined with IDs in <fonts>
    2. Cell styles (cellXfs) reference font IDs
    3. Cells reference cell style IDs via the 's' attribute

    Args:
        styles_path: Path to styles.xml file

    Returns:
        Set of cell style IDs (integers) that have strikethrough
    """
    tree = ET.parse(str(styles_path))
    root = tree.getroot()

    # Step 1: Find font IDs that have strikethrough
    strikethrough_font_ids = set()
    fonts = root.find('.//ss:fonts', NAMESPACES)

    if fonts is not None:
        for font_id, font in enumerate(fonts.findall('ss:font', NAMESPACES)):
            strike = font.find('ss:strike', NAMESPACES)
            if strike is not None:
                strikethrough_font_ids.add(font_id)

    logger.debug(f"Found {len(strikethrough_font_ids)} fonts with strikethrough: {strikethrough_font_ids}")

    # Step 2: Find cell style IDs that use these fonts
    strikethrough_style_ids = set()
    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)):
            # Check if this style references a strikethrough font
            font_id = xf.get('fontId')
            if font_id is not None and int(font_id) in strikethrough_font_ids:
                strikethrough_style_ids.add(style_id)

    logger.debug(f"Found {len(strikethrough_style_ids)} cell styles with strikethrough: {strikethrough_style_ids}")

    return strikethrough_style_ids


def _clear_cells_with_strikethrough_style(sheet_path: Path, strikethrough_style_ids: set) -> dict:
    """
    Clear content from cells that have strikethrough formatting.

    Args:
        sheet_path: Path to worksheet XML file
        strikethrough_style_ids: Set of style IDs that have strikethrough

    Returns:
        Dict with cells_cleared count
    """
    tree = ET.parse(str(sheet_path))
    root = tree.getroot()

    cells_cleared = 0

    # Find all cells in the worksheet
    for cell in root.findall('.//ss:c', NAMESPACES):
        # Check if cell has a style attribute
        style_id = cell.get('s')

        if style_id is not None and int(style_id) in strikethrough_style_ids:
            # This cell has strikethrough formatting - clear its content

            # Remove value elements
            for v in cell.findall('ss:v', NAMESPACES):
                cell.remove(v)

            # Remove inline string elements
            for is_elem in cell.findall('ss:is', NAMESPACES):
                cell.remove(is_elem)

            # Remove formula elements
            for f in cell.findall('ss:f', NAMESPACES):
                cell.remove(f)

            cells_cleared += 1

    # Write modified XML back to file
    if HAS_LXML:
        tree.write(
            str(sheet_path),
            encoding='utf-8',
            xml_declaration=True,
            pretty_print=False
        )
    else:
        tree.write(str(sheet_path), encoding='utf-8', xml_declaration=True)

    return {
        'cells_cleared': cells_cleared
    }


def _create_xlsx_from_directory(source_dir: Path, output_file: Path) -> None:
    """
    Create an Excel (.xlsx) file from a directory of extracted XML files.

    Args:
        source_dir: Directory containing extracted Excel XML files
        output_file: Path for output .xlsx file
    """
    with zipfile.ZipFile(output_file, 'w', zipfile.ZIP_DEFLATED) as zipf:
        # Walk through all files and add them to ZIP
        for file_path in source_dir.rglob('*'):
            if file_path.is_file():
                # Calculate archive name (relative path from source_dir)
                arcname = file_path.relative_to(source_dir)
                zipf.write(file_path, arcname)


def validate_excel_file(file_path: str) -> bool:
    """
    Validate that a file is a valid Excel file.

    Args:
        file_path: Path to file to validate

    Returns:
        True if valid Excel file, False otherwise
    """
    try:
        with zipfile.ZipFile(file_path, 'r') as zip_ref:
            # Check for required Excel components
            namelist = zip_ref.namelist()
            required = ['[Content_Types].xml', 'xl/workbook.xml']
            return all(item in namelist for item in required)
    except Exception as e:
        logger.error(f"Error validating Excel file: {e}")
        return False