"""
Unit tests for ExcelTranslator.

Tests cover:
- Text cell translation (strings only, not numbers/dates)
- Formula string extraction and translation
- Merged cell preservation
- Chart/data link preservation
- Error handling (corrupted, invalid format)
- Progress callback
- Provider integration
"""

import pytest
import tempfile
from pathlib import Path
from unittest.mock import Mock, MagicMock, patch
from typing import List

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, Reference

from translators.excel_translator import (
    ExcelTranslator,
    ExcelProcessorError,
    excel_translator,
)
from services.providers.schemas import TranslationRequest, TranslationResponse


class MockTranslationProvider:
    """Mock translation provider for testing."""

    def __init__(self, translations: dict = None):
        self._translations = translations or {}
        self._call_count = 0
        self._requests_received: List[TranslationRequest] = []

    def get_name(self) -> str:
        return "mock"

    def is_available(self) -> bool:
        return True

    def translate_text(self, request: TranslationRequest) -> TranslationResponse:
        self._call_count += 1
        self._requests_received.append(request)

        text = request.text
        translated = self._translations.get(text, f"TR_{text}")

        return TranslationResponse(
            translated_text=translated,
            provider_name="mock",
            source_language=request.source_language,
        )

    def translate_batch(
        self, requests: List[TranslationRequest]
    ) -> List[TranslationResponse]:
        return [self.translate_text(req) for req in requests]


class TestExcelProcessorError:
    """Tests for ExcelProcessorError exception."""

    def test_error_with_default_message(self):
        """Test error with default message from code."""
        error = ExcelProcessorError(ExcelProcessorError.INVALID_FORMAT)
        assert error.code == "INVALID_FORMAT"
        assert "xlsx" in error.message.lower()
        assert error.to_dict()["error"] == "INVALID_FORMAT"

    def test_error_with_custom_message(self):
        """Test error with custom message."""
        error = ExcelProcessorError(
            ExcelProcessorError.EXCEL_CORRUPTED, message="Custom error message"
        )
        assert error.message == "Custom error message"

    def test_error_with_details(self):
        """Test error with details dict."""
        error = ExcelProcessorError(
            ExcelProcessorError.EXCEL_TOO_LARGE, details={"size_mb": 100, "max_mb": 50}
        )
        assert error.details["size_mb"] == 100
        assert error.to_dict()["details"]["size_mb"] == 100

    def test_all_error_codes_have_messages(self):
        """Test all error codes have default messages."""
        codes = [
            ExcelProcessorError.INVALID_FORMAT,
            ExcelProcessorError.EXCEL_CORRUPTED,
            ExcelProcessorError.EXCEL_READ_ERROR,
            ExcelProcessorError.EXCEL_WRITE_ERROR,
            ExcelProcessorError.EXCEL_TOO_LARGE,
        ]
        for code in codes:
            error = ExcelProcessorError(code)
            assert error.message
            assert len(error.message) > 0


class TestExcelTranslatorInit:
    """Tests for ExcelTranslator initialization."""

    def test_init_without_provider(self):
        """Test initialization without provider (uses legacy fallback)."""
        translator = ExcelTranslator()
        assert translator._provider is None

    def test_init_with_provider(self):
        """Test initialization with provider."""
        mock_provider = MockTranslationProvider()
        translator = ExcelTranslator(provider=mock_provider)
        assert translator._provider is mock_provider

    def test_set_provider(self):
        """Test setting provider after initialization."""
        translator = ExcelTranslator()
        mock_provider = MockTranslationProvider()
        translator.set_provider(mock_provider)
        assert translator._provider is mock_provider

    def test_set_custom_prompt(self):
        """Test setting custom prompt."""
        translator = ExcelTranslator()
        translator.set_custom_prompt("Translate to French")
        assert translator._custom_prompt == "Translate to French"


class TestFileValidation:
    """Tests for file validation."""

    def test_validate_nonexistent_file(self):
        """Test validation of non-existent file."""
        translator = ExcelTranslator()
        with pytest.raises(ExcelProcessorError) as exc_info:
            translator._validate_file(Path("/nonexistent/file.xlsx"))
        assert exc_info.value.code == ExcelProcessorError.EXCEL_READ_ERROR

    def test_validate_wrong_extension(self, tmp_path):
        """Test validation of file with wrong extension."""
        translator = ExcelTranslator()
        wrong_file = tmp_path / "test.txt"
        wrong_file.write_text("not an excel file")

        with pytest.raises(ExcelProcessorError) as exc_info:
            translator._validate_file(wrong_file)
        assert exc_info.value.code == ExcelProcessorError.INVALID_FORMAT

    def test_validate_invalid_magic_bytes(self, tmp_path):
        """Test validation of file with invalid magic bytes."""
        translator = ExcelTranslator()
        invalid_file = tmp_path / "test.xlsx"
        invalid_file.write_bytes(b"Not a ZIP file")

        with pytest.raises(ExcelProcessorError) as exc_info:
            translator._validate_file(invalid_file)
        assert exc_info.value.code == ExcelProcessorError.INVALID_FORMAT

    def test_validate_file_too_large(self, tmp_path):
        """Test validation of file exceeding size limit."""
        translator = ExcelTranslator()
        translator.MAX_FILE_SIZE_MB = 0.001  # Set very low limit for testing

        wb = Workbook()
        large_file = tmp_path / "large.xlsx"
        wb.save(large_file)

        with pytest.raises(ExcelProcessorError) as exc_info:
            translator._validate_file(large_file)
        assert exc_info.value.code == ExcelProcessorError.EXCEL_TOO_LARGE

        translator.MAX_FILE_SIZE_MB = 50  # Reset

    def test_validate_valid_file(self, tmp_path):
        """Test validation of valid file."""
        translator = ExcelTranslator()

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Test"
        valid_file = tmp_path / "valid.xlsx"
        wb.save(valid_file)

        translator._validate_file(valid_file)


class TestTextCellTranslation:
    """Tests for text cell translation (AC1)."""

    def test_translate_string_cells(self, tmp_path):
        """Test that string cells are translated."""
        mock_provider = MockTranslationProvider(
            {
                "Hello": "Bonjour",
                "World": "Monde",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hello"
        ws["B1"] = "World"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert ws_out["A1"].value == "Bonjour"
        assert ws_out["B1"].value == "Monde"

    def test_numbers_not_translated(self, tmp_path):
        """Test that numbers are NOT translated."""
        mock_provider = MockTranslationProvider()
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = 123
        ws["A2"] = 45.67
        ws["A3"] = "Text"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert ws_out["A1"].value == 123
        assert ws_out["A2"].value == 45.67

    def test_empty_cells_not_translated(self, tmp_path):
        """Test that empty cells are not translated."""
        mock_provider = MockTranslationProvider()
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = None
        ws["A2"] = ""
        ws["A3"] = "   "
        ws["A4"] = "Text"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        assert mock_provider._call_count == 2


class TestFormulaPreservation:
    """Tests for formula preservation (AC2)."""

    def test_formula_preserved_strings_translated(self, tmp_path):
        """Test that formulas are preserved and strings inside are translated."""
        mock_provider = MockTranslationProvider(
            {
                "Total: ": "Somme: ",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = 10
        ws["A2"] = 20
        ws["A3"] = "=SUM(A1:A2)"
        ws["A4"] = '=CONCAT("Total: ", A3)'

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert ws_out["A3"].value == "=SUM(A1:A2)"
        assert ws_out["A4"].value == '=CONCAT("Somme: ", A3)'

    def test_formula_without_strings_unchanged(self, tmp_path):
        """Test that formulas without strings are unchanged."""
        mock_provider = MockTranslationProvider()
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = 10
        ws["A2"] = 20
        ws["A3"] = "=SUM(A1:A2)"
        ws["A4"] = "=A1*A2"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert ws_out["A3"].value == "=SUM(A1:A2)"
        assert ws_out["A4"].value == "=A1*A2"

    def test_formula_with_single_quotes(self, tmp_path):
        """Test that single-quoted strings in formulas are translated."""
        mock_provider = MockTranslationProvider(
            {
                "yes": "oui",
                "no": "non",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "yes"
        ws["A2"] = '=IF(A1="yes", "yes", "no")'

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        # Double-quoted strings should be translated
        assert "oui" in ws_out["A2"].value or "non" in ws_out["A2"].value

    def test_formula_with_escaped_quotes(self, tmp_path):
        """Test that escaped quotes in formulas are handled correctly."""
        mock_provider = MockTranslationProvider(
            {
                'He said "hello"': 'Il a dit "bonjour"',
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = '=CONCAT("He said ""hello""", " world")'

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        # Formula should remain valid after translation
        formula = ws_out["A1"].value
        assert formula.startswith("=")
        # Should contain the translated text with properly escaped quotes
        assert "bonjour" in formula or "He said" in formula


class TestMergedCellPreservation:
    """Tests for merged cell preservation (AC3)."""

    def test_merged_cells_preserved(self, tmp_path):
        """Test that merged cells are preserved after translation."""
        mock_provider = MockTranslationProvider({"Merged Title": "Titre Fusionne"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Merged Title"
        ws.merge_cells("A1:C1")

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert "A1:C1" in [str(r) for r in ws_out.merged_cells.ranges]
        assert ws_out["A1"].value == "Titre Fusionne"


class TestFormattingPreservation:
    """Tests for formatting preservation (AC5)."""

    def test_cell_formatting_preserved(self, tmp_path):
        """Test that cell formatting is preserved after translation."""
        mock_provider = MockTranslationProvider({"Bold Red": "Gras Rouge"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Bold Red"
        ws["A1"].font = Font(bold=True, color="FF0000")
        ws["A1"].fill = PatternFill(
            start_color="FFFF00", end_color="FFFF00", fill_type="solid"
        )
        ws["A1"].alignment = Alignment(horizontal="center")

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert ws_out["A1"].value == "Gras Rouge"
        assert ws_out["A1"].font.bold is True
        assert ws_out["A1"].font.color.rgb.endswith("FF0000")
        assert ws_out["A1"].fill.start_color.rgb.endswith("FFFF00")
        assert ws_out["A1"].alignment.horizontal == "center"


class TestChartPreservation:
    """Tests for chart preservation (AC4)."""

    def test_chart_preserved(self, tmp_path):
        """Test that charts are preserved after translation."""
        mock_provider = MockTranslationProvider()
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active

        ws["A1"] = "Category"
        ws["B1"] = "Value"
        ws["A2"] = "A"
        ws["B2"] = 10
        ws["A3"] = "B"
        ws["B3"] = 20

        chart = BarChart()
        chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=3))
        chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=3))
        ws.add_chart(chart, "D1")

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        assert len(ws_out._charts) == 1

    def test_chart_data_links_intact(self, tmp_path):
        """Test that chart data links remain functional after translation."""
        mock_provider = MockTranslationProvider(
            {
                "Category": "Catégorie",
                "Value": "Valeur",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active

        # Set up data with labels that will be translated
        ws["A1"] = "Category"
        ws["B1"] = "Value"
        ws["A2"] = "A"
        ws["B2"] = 10
        ws["A3"] = "B"
        ws["B3"] = 20

        chart = BarChart()
        data_ref = Reference(ws, min_col=2, min_row=1, max_row=3)
        cats_ref = Reference(ws, min_col=1, min_row=2, max_row=3)
        chart.add_data(data_ref, titles_from_data=True)
        chart.set_categories(cats_ref)
        ws.add_chart(chart, "D1")

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        ws_out = wb_out.active

        # Verify chart exists and has data references
        assert len(ws_out._charts) == 1
        chart_out = ws_out._charts[0]

        # Verify data is still linked (chart should have series)
        assert len(chart_out.series) > 0

        # Verify the header row text was translated
        assert ws_out["A1"].value == "Catégorie"
        assert ws_out["B1"].value == "Valeur"

        # Verify numeric data is unchanged (data links intact)
        assert ws_out["B2"].value == 10
        assert ws_out["B3"].value == 20


class TestProgressCallback:
    """Tests for progress callback (AC8 - NFR3)."""

    def test_progress_callback_called(self, tmp_path):
        """Test that progress callback is called."""
        mock_provider = MockTranslationProvider({"Test": "Test FR"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Test"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        progress_events = []

        def callback(event):
            progress_events.append(event)

        translator.translate_file(
            input_file, output_file, "fr", progress_callback=callback
        )

        assert len(progress_events) >= 1
        assert "sheet" in progress_events[0]
        assert "total" in progress_events[0]
        assert "cells_translated" in progress_events[0]

    def test_progress_callback_without_callback(self, tmp_path):
        """Test that translation works without callback."""
        mock_provider = MockTranslationProvider({"Test": "Test FR"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Test"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        result = translator.translate_file(input_file, output_file, "fr")
        assert result == output_file


class TestProviderIntegration:
    """Tests for provider integration (AC8)."""

    def test_provider_receives_correct_requests(self, tmp_path):
        """Test that provider receives correctly formatted requests."""
        mock_provider = MockTranslationProvider({"Hello": "Bonjour"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hello"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr", source_language="en")

        assert len(mock_provider._requests_received) >= 1
        req = mock_provider._requests_received[0]
        assert req.text == "Hello"
        assert req.target_language == "fr"
        assert req.source_language == "en"

    def test_custom_prompt_passed_to_provider(self, tmp_path):
        """Test that custom prompt is passed via metadata."""
        mock_provider = MockTranslationProvider({"Hello": "Bonjour"})
        translator = ExcelTranslator(provider=mock_provider)
        translator.set_custom_prompt("Translate to formal French")

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hello"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        req = mock_provider._requests_received[0]
        assert req.metadata is not None
        assert req.metadata.get("custom_prompt") == "Translate to formal French"


class TestErrorHandling:
    """Tests for error handling (AC7)."""

    def test_corrupted_file_error(self, tmp_path):
        """Test that corrupted file raises ExcelProcessorError."""
        translator = ExcelTranslator()

        corrupted_file = tmp_path / "corrupted.xlsx"
        corrupted_file.write_bytes(b"PK\x03\x04" + b"\x00" * 100)

        with pytest.raises(ExcelProcessorError) as exc_info:
            translator.translate_file(corrupted_file, tmp_path / "out.xlsx", "fr")

        assert exc_info.value.code in [
            ExcelProcessorError.EXCEL_CORRUPTED,
            ExcelProcessorError.EXCEL_READ_ERROR,
        ]

    def test_invalid_format_error_details(self, tmp_path):
        """Test that invalid format error includes details."""
        translator = ExcelTranslator()

        invalid_file = tmp_path / "test.txt"
        invalid_file.write_text("not excel")

        with pytest.raises(ExcelProcessorError) as exc_info:
            translator._validate_file(invalid_file)

        error = exc_info.value
        assert error.to_dict()["error"] == "INVALID_FORMAT"
        assert "details" in error.to_dict()


class TestLegacyFallback:
    """Tests for legacy translation_service fallback."""

    def test_fallback_to_legacy_service(self, tmp_path):
        """Test that legacy service is used when no provider set."""
        translator = ExcelTranslator()

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hello"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        with patch("services.translation_service.translation_service") as mock_service:
            mock_service.translate_batch.return_value = ["Bonjour"]

            translator.translate_file(input_file, output_file, "fr")

            mock_service.translate_batch.assert_called_once()

    def test_global_instance_exists(self):
        """Test that global excel_translator instance exists."""
        from translators import excel_translator

        assert excel_translator is not None
        assert isinstance(excel_translator, ExcelTranslator)


class TestExcelCompatibility:
    """Tests for Excel compatibility (AC6)."""

    def test_valid_xlsx_structure(self, tmp_path):
        """Test that output file has valid xlsx structure that Excel can open."""
        mock_provider = MockTranslationProvider({"Test": "TestFR"})
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Test"
        ws["B1"] = 123
        ws["A2"] = "=SUM(B1:B1)"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        # Verify file is valid ZIP (xlsx format)
        import zipfile

        assert zipfile.is_zipfile(output_file), "Output is not a valid xlsx (ZIP) file"

        # Verify it can be opened by openpyxl without errors
        wb_out = load_workbook(output_file)
        assert wb_out is not None

        # Verify it has required xlsx structure
        with zipfile.ZipFile(output_file, "r") as zf:
            files = zf.namelist()
            assert "[Content_Types].xml" in files, "Missing Content_Types.xml"
            assert any("xl/workbook.xml" in f for f in files), "Missing workbook.xml"

    def test_complex_workbook_compatibility(self, tmp_path):
        """Test compatibility with complex workbooks containing formulas, formatting, and charts."""
        mock_provider = MockTranslationProvider(
            {
                "Sales": "Ventes",
                "Q1": "T1",
                "Total": "Total",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws.title = "Sales"

        # Add headers with formatting
        ws["A1"] = "Sales"
        ws["B1"] = "Q1"
        ws["A2"] = "Product A"
        ws["B2"] = 100
        ws["A3"] = "Product B"
        ws["B3"] = 200
        ws["A4"] = "Total"
        ws["B4"] = "=SUM(B2:B3)"

        # Add chart
        chart = BarChart()
        chart.add_data(Reference(ws, min_col=2, min_row=1, max_row=3))
        chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=3))
        ws.add_chart(chart, "D1")

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        # Verify complex file opens successfully
        wb_out = load_workbook(output_file, data_only=False)
        ws_out = wb_out.active

        assert ws_out.title == "Ventes"
        assert ws_out["A1"].value == "Ventes"
        assert ws_out["B4"].value == "=SUM(B2:B3)"  # Formula preserved
        assert len(ws_out._charts) == 1  # Chart preserved


class TestMultipleSheets:
    """Tests for multi-sheet workbooks."""

    def test_multiple_sheets_translated(self, tmp_path):
        """Test that all sheets are translated."""
        mock_provider = MockTranslationProvider(
            {
                "Sheet1Text": "Feuille1Texte",
                "Sheet2Text": "Feuille2Texte",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws1 = wb.active
        ws1["A1"] = "Sheet1Text"

        ws2 = wb.create_sheet("Sheet2")
        ws2["A1"] = "Sheet2Text"

        input_file = tmp_path / "input.xlsx"
        output_file = tmp_path / "output.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)

        assert wb_out.worksheets[0]["A1"].value == "Feuille1Texte"
        assert wb_out.worksheets[1]["A1"].value == "Feuille2Texte"


class TestColumnWidthAndAlignment:
    """Tests for intelligent column width adjustment and RTL alignment."""

    def test_column_width_widened_for_long_translation(self, tmp_path):
        """Test that narrow columns are widened when translated text expands."""
        mock_provider = MockTranslationProvider(
            {
                "Hi": "This is a much longer translated sentence that requires more width",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hi"
        ws.column_dimensions["A"].width = 8.0

        input_file = tmp_path / "narrow.xlsx"
        output_file = tmp_path / "widened.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fr")

        wb_out = load_workbook(output_file)
        new_width = wb_out.active.column_dimensions["A"].width
        assert new_width is not None
        assert new_width > 15.0

    def test_cell_alignment_flipped_for_rtl(self, tmp_path):
        """Test that explicit left alignment is converted to right alignment in RTL."""
        from openpyxl.styles import Alignment

        mock_provider = MockTranslationProvider(
            {
                "Hello": "سلام",
            }
        )
        translator = ExcelTranslator(provider=mock_provider)

        wb = Workbook()
        ws = wb.active
        ws["A1"] = "Hello"
        ws["A1"].alignment = Alignment(horizontal="left")

        input_file = tmp_path / "ltr_align.xlsx"
        output_file = tmp_path / "rtl_align.xlsx"
        wb.save(input_file)

        translator.translate_file(input_file, output_file, "fa")

        wb_out = load_workbook(output_file)
        cell_align = wb_out.active["A1"].alignment
        assert cell_align.horizontal == "right"
