"""pubsalesacc/utils/excel_fix.py — Fix malformed Sony Publishing XLSX files.

Sony Publishing sales files sometimes contain invalid font family values in
xl/styles.xml that prevent Python/openpyxl from opening them. This module
patches the styles XML inside the XLSX ZIP archive in-place.

Reference:
    https://stackoverflow.com/questions/69343910/cannot-read-excel-file-via-python-workarounds
"""

import logging
import os
import tempfile
import zipfile
from pathlib import Path

logger = logging.getLogger(__name__)

_STYLES_PATH = "xl/styles.xml"
_REPLACEMENTS = [
    ("ARIAL", "Arial"),
    ('family val="16"', 'family val="2"'),
]


def fix_sony_excel(filepath: str | Path) -> Path:
    """Patch a malformed Sony Publishing XLSX file so it can be read by pandas/openpyxl.

    Creates a .bak backup of the original, then rewrites the archive with a
    corrected xl/styles.xml.

    Args:
        filepath: Path to the .xlsx file to fix.

    Returns:
        Path to the fixed file (same path as input).

    Raises:
        FileNotFoundError: If filepath does not exist.
        zipfile.BadZipFile: If the file is not a valid ZIP/XLSX.
    """
    filepath = Path(filepath)
    if not filepath.exists():
        raise FileNotFoundError(f"File not found: {filepath}")

    backup = filepath.with_suffix(filepath.suffix + ".bak")
    logger.info("Fixing Sony Excel file: %s", filepath.name)

    # Extract and patch styles.xml in a temp directory
    with tempfile.TemporaryDirectory() as tmpdir:
        styles_tmp = Path(tmpdir) / "styles.xml"

        with zipfile.ZipFile(filepath) as z:
            z.extract(_STYLES_PATH, path=tmpdir)

        xml = (Path(tmpdir) / _STYLES_PATH).read_text(encoding="utf-8")
        for old, new in _REPLACEMENTS:
            xml = xml.replace(old, new)
        styles_tmp.write_text(xml, encoding="utf-8")

        # Backup original, write patched archive
        os.replace(filepath, backup)
        logger.info("Backup created: %s", backup.name)

        with (
            zipfile.ZipFile(backup, "r") as zin,
            zipfile.ZipFile(filepath, "w", compression=zipfile.ZIP_DEFLATED) as zout,
        ):
            for item in zin.infolist():
                if item.filename == _STYLES_PATH:
                    zout.write(styles_tmp, _STYLES_PATH)
                else:
                    zout.writestr(item, zin.read(item.filename))

    logger.info("Excel fix complete: %s", filepath.name)
    return filepath