# region OrchestratorTests [TYPE Module]
# @SEMANTICS: test, translate, orchestrator, events
# @PURPOSE: Tests for TranslationOrchestrator: run lifecycle, partial failure, batch retry, event invariants, NULL handling.
# @LAYER Test
# @RELATION  BINDS_TO -> [TranslationOrchestrator]
# @RELATION  BINDS_TO -> [TranslationEventLog]
# @TEST_CONTRACT  TranslationOrchestrator -> start_run, execute_run, cancel_run, retry_failed_batches
# @TEST_FIXTURE  mock_db -> MagicMock SQLAlchemy session
# @TEST_FIXTURE  mock_config_manager -> MagicMock ConfigManager
# @TEST_EDGE  missing_preview -> raises ValueError
# @TEST_EDGE  invalid_run_status -> raises ValueError
# @TEST_EDGE  executor_failure -> run is marked FAILED

from datetime import UTC, datetime
import json
import pytest
from unittest.mock import MagicMock, patch

from src.models.translate import (
    TranslationJob,
    TranslationLanguage,
    TranslationPreviewSession,
    TranslationRecord,
    TranslationRun,
    TranslationRunLanguageStats,
)
from src.plugins.translate._llm_call import LLMTranslationService
from src.plugins.translate.events import TranslationEventLog
from src.plugins.translate.executor import TranslationExecutor
from src.plugins.translate.orchestrator import TranslationOrchestrator


# region mock_job [TYPE Function]
# @PURPOSE: Create a mock TranslationJob with standard config.
@pytest.fixture
def mock_job() -> MagicMock:
    job = MagicMock(spec=TranslationJob)
    job.id = "job-123"
    job.name = "Test Job"
    job.status = "ACTIVE"
    job.source_dialect = "postgresql"
    job.target_dialect = "postgresql"
    job.database_dialect = "postgresql"
    job.source_datasource_id = "42"
    job.translation_column = "name"
    job.context_columns = ["category"]
    job.target_schema = "public"
    job.target_table = "translated_data"
    job.target_key_cols = ["id"]
    job.source_key_cols = ["id"]
    job.source_table = "source_data"
    job.target_languages = ["en"]
    job.provider_id = "provider-1"
    job.batch_size = 50
    job.upsert_strategy = "MERGE"
    job.target_column = None
    return job


# endregion mock_job


# region mock_preview_session [TYPE Function]
# @PURPOSE: Create a mock accepted preview session.
@pytest.fixture
def mock_preview_session() -> MagicMock:
    session = MagicMock(spec=TranslationPreviewSession)
    session.id = "session-1"
    session.job_id = "job-123"
    session.status = "APPLIED"
    session.created_at = datetime.now(UTC)
    return session


# endregion mock_preview_session


# region TestTranslationEventLog [TYPE Class]
# @PURPOSE: Tests for TranslationEventLog terminal event invariant.
class TestTranslationEventLog:

    # region test_log_event_creates_record [TYPE Function]
    # @PURPOSE: log_event creates a TranslationEvent row.
    def test_log_event_creates_record(self) -> None:
        db = MagicMock()
        log = TranslationEventLog(db)
        event = log.log_event(
            job_id="job-1",
            event_type="RUN_STARTED",
            payload={"key": "value"},
            run_id="run-1",
        )
        assert event is not None
        assert event.event_type == "RUN_STARTED"
        db.add.assert_called_once()
        db.flush.assert_called_once()

    # region test_invalid_event_type_raises [TYPE Function]
    # @PURPOSE: Invalid event type raises ValueError.
    def test_invalid_event_type_raises(self) -> None:
        db = MagicMock()
        log = TranslationEventLog(db)
        with pytest.raises(ValueError, match="Invalid event_type"):
            log.log_event(
                job_id="job-1",
                event_type="UNKNOWN_EVENT",
            )

    # region test_terminal_event_invariant [TYPE Function]
    # @PURPOSE: Only one terminal event allowed per run.
    def test_terminal_event_invariant(self) -> None:
        db = MagicMock()
        # Mock existing terminal event
        db.query.return_value.filter.return_value.first.return_value = ("event-1",)

        log = TranslationEventLog(db)
        with pytest.raises(ValueError, match="already has a terminal event"):
            log.log_event(
                job_id="job-1",
                run_id="run-1",
                event_type="RUN_COMPLETED",
            )

    # region test_run_started_must_precede_other_events [TYPE Function]
    # @PURPOSE: Other run events require RUN_STARTED to exist first.
    def test_run_started_must_precede_other_events(self) -> None:
        db = MagicMock()
        # Mock no RUN_STARTED event
        db.query.return_value.filter.return_value.first.side_effect = [None, None]

        log = TranslationEventLog(db)
        with pytest.raises(ValueError, match="RUN_STARTED event must precede"):
            log.log_event(
                job_id="job-1",
                run_id="run-1",
                event_type="BATCH_STARTED",
            )

    # region test_prune_expired_creates_snapshot [TYPE Function]
    # @PURPOSE: prune_expired creates MetricSnapshot before deleting.
    def test_prune_expired_creates_snapshot(self) -> None:
        db = MagicMock()
        # Mock that there are expired events
        db.query.return_value.filter.return_value.count.return_value = 10
        # Use side_effect to simulate data being consumed by deletion.
        # The while loop re-queries; first call returns items, second returns empty.
        db.query.return_value.filter.return_value.limit.return_value.all.side_effect = [
            [("e1",), ("e2",)],
            [],
        ]

        log = TranslationEventLog(db)
        result = log.prune_expired(retention_days=90)
        assert result["pruned"] >= 0

    # endregion test_log_event_creates_record
    # endregion test_invalid_event_type_raises
    # endregion test_terminal_event_invariant
    # endregion test_run_started_must_precede_other_events
    # endregion test_prune_expired_creates_snapshot
# endregion TestTranslationEventLog


# region TestTranslationOrchestrator [TYPE Class]
# @PURPOSE: Tests for TranslationOrchestrator run lifecycle.
class TestTranslationOrchestrator:

    # region test_start_run_success [TYPE Function]
    # @PURPOSE: start_run creates a PENDING run with event.
    def test_start_run_success(
        self,
        mock_job: MagicMock,
        mock_preview_session: MagicMock,
    ) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job query
        db.query.return_value.filter.return_value.first.return_value = mock_job

        # Mock accepted preview session
        preview_query = (
            db.query.return_value.filter.return_value
        )
        preview_query.order_by.return_value.first.return_value = mock_preview_session

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        run = orch.start_run(job_id="job-123")

        assert run.status == "PENDING"
        assert run.job_id == "job-123"

    # region test_start_run_with_datasource_no_preview_succeeds [TYPE Function]
    # @PURPOSE: Manual run with Superset datasource succeeds even without accepted preview session.
    def test_start_run_with_datasource_no_preview_succeeds(
        self,
        mock_job: MagicMock,
    ) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job query — job HAS a Superset datasource (fixture sets source_datasource_id="42")
        db.query.return_value.filter.return_value.first.return_value = mock_job

        # No accepted preview session mock — not needed because datasource bypasses preview check.
        # validate_job_preconditions skips preview query when source_datasource_id is set.
        # This verifies the gating logic: datasource → no preview required → run proceeds.

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        run = orch.start_run(job_id="job-123")

        assert run.status == "PENDING"
        assert run.job_id == "job-123"

    # region test_start_run_missing_preview_raises [TYPE Function]
    # @PURPOSE: Manual run without accepted preview raises ValueError when job has no direct datasource.
    def test_start_run_missing_preview_raises(
        self,
        mock_job: MagicMock,
    ) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job query — job WITHOUT a Superset datasource, so preview is required
        mock_job.source_datasource_id = None
        mock_job.environment_id = None
        db.query.return_value.filter.return_value.first.return_value = mock_job

        # No accepted preview session
        db.query.return_value.filter.return_value.order_by.return_value.first.return_value = None

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="no accepted preview"):
            orch.start_run(job_id="job-123")

    # region test_start_run_draft_job_raises [TYPE Function]
    # @PURPOSE: Draft job cannot be run.
    def test_start_run_draft_job_raises(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        draft_job = MagicMock(spec=TranslationJob)
        draft_job.status = "DRAFT"

        db.query.return_value.filter.return_value.first.return_value = draft_job

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="DRAFT"):
            orch.start_run(job_id="job-123")

    # region test_execute_run_invalid_status [TYPE Function]
    # @PURPOSE: Cannot execute a run that is not in PENDING status.
    def test_execute_run_invalid_status(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.status = "COMPLETED"
        run.job_id = "job-123"

        db.query.return_value.filter.return_value.first.return_value = mock_job

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="PENDING"):
            orch.execute_run(run)

    # region test_execute_run_success [TYPE Function]
    # @BRIEF Happy path: executor completes, SQL generated, Superset submits.
    def test_execute_run_success(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()
        mock_job.target_table = "target_tbl"

        # Mock job query — first call returns mock_job, final re-query returns completed_run
        completed_run_response = MagicMock()
        completed_run_response.id = "run-1"
        completed_run_response.job_id = "job-123"
        completed_run_response.status = "COMPLETED"
        completed_run_response.total_records = 10
        completed_run_response.successful_records = 10
        completed_run_response.failed_records = 0
        completed_run_response.skipped_records = 0
        completed_run_response.completed_at = datetime.now(UTC)
        completed_run_response.error_message = None
        completed_run_response.insert_status = "success"
        completed_run_response.superset_execution_id = "q-1"
        completed_run_response.superset_execution_log = {}
        # query job -> mock_job, query TranslationRun -> completed_run_response
        db.query.return_value.filter.return_value.first.side_effect = [
            mock_job,
            completed_run_response,
        ]

        # Create a PENDING input run
        run = MagicMock()
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "PENDING"

        with patch(
            "src.plugins.translate.orchestrator_exec.TranslationExecutor"
        ) as MockExecutor:
            mock_executor_instance = MagicMock()
            mock_executor_instance.execute_run.return_value = completed_run_response
            MockExecutor.return_value = mock_executor_instance

            orch = TranslationOrchestrator(db, config_manager, "test-user")
            engine = orch._runner._executor_engine
            with patch.object(engine._sql_service, 'generate_and_insert_sql',
                              return_value={"status": "success", "query_id": "q-1", "rows_affected": 10}), \
                 patch.object(engine._aggregator, 'update_language_stats'), \
                 patch.object(engine, 'event_log'):
                result = orch.execute_run(run)

        assert result.status == "COMPLETED"
        assert result.insert_status == "success"
        assert result.superset_execution_id == "q-1"
        assert result.total_records == 10
        assert result.successful_records == 10

    # endregion test_execute_run_success

    # region test_execute_run_executor_failure [TYPE Function]
    # @BRIEF Executor raises exception, run is marked FAILED.
    def test_execute_run_executor_failure(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job query
        db.query.return_value.filter.return_value.first.return_value = mock_job

        run = MagicMock()
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "PENDING"
        run.error_message = None

        with patch(
            "src.plugins.translate.orchestrator_exec.TranslationExecutor"
        ) as MockExecutor:
            mock_executor_instance = MagicMock()
            mock_executor_instance.execute_run.side_effect = ValueError(
                "LLM provider unavailable"
            )
            MockExecutor.return_value = mock_executor_instance

            orch = TranslationOrchestrator(db, config_manager, "test-user")
            with patch.object(orch._runner._executor_engine, "event_log"):
                result = orch.execute_run(run)

        assert result.status == "FAILED"
        assert "LLM provider unavailable" in (result.error_message or "")

    # endregion test_execute_run_executor_failure

    # region test_execute_run_skip_insert [TYPE Function]
    # @BRIEF skip_insert=True completes run without SQL generation or Superset submission.
    def test_execute_run_skip_insert(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        db.query.return_value.filter.return_value.first.return_value = mock_job

        run = MagicMock()
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "PENDING"
        run.completed_at = None

        completed_run = MagicMock()
        completed_run.id = "run-1"
        completed_run.job_id = "job-123"
        completed_run.status = "COMPLETED"
        completed_run.total_records = 5
        completed_run.successful_records = 5
        completed_run.failed_records = 0
        completed_run.skipped_records = 0
        completed_run.completed_at = datetime.now(UTC)
        completed_run.error_message = None

        with patch(
            "src.plugins.translate.orchestrator_exec.TranslationExecutor"
        ) as MockExecutor:
            mock_executor_instance = MagicMock()
            mock_executor_instance.execute_run.return_value = completed_run
            MockExecutor.return_value = mock_executor_instance

            orch = TranslationOrchestrator(db, config_manager, "test-user")
            engine = orch._runner._executor_engine
            with patch.object(engine._sql_service, 'generate_and_insert_sql') as mock_gen_sql, \
                 patch.object(engine._aggregator, 'update_language_stats'), \
                 patch.object(engine, 'event_log'):
                result = orch.execute_run(run, skip_insert=True)

        assert result.status == "COMPLETED"
        # generate_and_insert_sql should NOT be called in skip_insert mode
        mock_gen_sql.assert_not_called()
        assert result.total_records == 5

    # endregion test_execute_run_skip_insert

    # region test_execute_run_no_job [TYPE Function]
    # @BRIEF Job not found raises ValueError.
    def test_execute_run_no_job(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job query returns None (job not found)
        db.query.return_value.filter.return_value.first.return_value = None

        run = MagicMock()
        run.id = "run-1"
        run.job_id = "job-missing"
        run.status = "PENDING"

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="not found"):
            orch.execute_run(run)

    # endregion test_execute_run_no_job

    # region test_execute_run_insert_failure [TYPE Function]
    # @BRIEF SQL generation/insert returns failure — run marked FAILED with error logged.
    def test_execute_run_insert_failure(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # First query returns mock_job, re-query returns completed_run
        completed_run_response = MagicMock()
        completed_run_response.id = "run-1"
        completed_run_response.job_id = "job-123"
        completed_run_response.status = "COMPLETED"
        completed_run_response.total_records = 3
        completed_run_response.successful_records = 3
        completed_run_response.failed_records = 0
        completed_run_response.skipped_records = 0
        completed_run_response.completed_at = datetime.now(UTC)
        completed_run_response.error_message = None
        completed_run_response.insert_status = "failed"
        completed_run_response.superset_execution_id = ""
        completed_run_response.superset_execution_log = {}
        db.query.return_value.filter.return_value.first.side_effect = [
            mock_job,
            completed_run_response,
        ]

        run = MagicMock()
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "PENDING"

        with patch(
            "src.plugins.translate.orchestrator_exec.TranslationExecutor"
        ) as MockExecutor:
            mock_executor_instance = MagicMock()
            mock_executor_instance.execute_run.return_value = completed_run_response
            MockExecutor.return_value = mock_executor_instance

            orch = TranslationOrchestrator(db, config_manager, "test-user")
            engine = orch._runner._executor_engine
            with patch.object(engine._sql_service, 'generate_and_insert_sql',
                              return_value={"status": "failed", "error_message": "timeout", "query_id": None}), \
                 patch.object(engine._aggregator, 'update_language_stats'), \
                 patch.object(engine, 'event_log'):
                result = orch.execute_run(run)

        assert result.status == "FAILED"
        assert result.insert_status == "failed"
        assert "timeout" in (result.error_message or "")

    # endregion test_execute_run_insert_failure

    # region test_cancel_run [TYPE Function]
    # @PURPOSE: Cancel a run changes status to CANCELLED.
    def test_cancel_run(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "RUNNING"

        # Chain: first query returns run, event-log queries return None (no existing terminal events)
        db.query.return_value.filter.return_value.first.side_effect = [run, None]

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        result = orch.cancel_run("run-1")

        assert result.status == "CANCELLED"

    # region test_cancel_run_invalid_status [TYPE Function]
    # @PURPOSE: Cannot cancel a completed run.
    def test_cancel_run_invalid_status(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.status = "COMPLETED"

        db.query.return_value.filter.return_value.first.return_value = run

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="cancelled"):
            orch.cancel_run("run-1")

    # region test_retry_failed_batches_no_failures [TYPE Function]
    # @PURPOSE: Raises if no failed batches found.
    def test_retry_failed_batches_no_failures(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "COMPLETED"

        # Mock run query
        db.query.return_value.filter.return_value.first.return_value = run

        # No failed batches
        db.query.return_value.filter.return_value.all.return_value = []

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        with pytest.raises(ValueError, match="No failed batches"):
            orch.retry_failed_batches("run-1")

    # region test_get_run_status [TYPE Function]
    # @PURPOSE: get_run_status returns structured status.
    def test_get_run_status(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "COMPLETED"
        run.started_at = datetime.now(UTC)
        run.completed_at = datetime.now(UTC)
        run.error_message = None
        run.total_records = 100
        run.successful_records = 95
        run.failed_records = 3
        run.skipped_records = 2
        run.insert_status = "success"
        run.superset_execution_id = "query-1"
        run.created_by = "test-user"
        run.created_at = datetime.now(UTC)

        db.query.return_value.filter.return_value.first.return_value = run
        db.query.return_value.filter.return_value.count.return_value = 5

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        status = orch.get_run_status("run-1")

        assert status["id"] == "run-1"
        assert status["status"] == "COMPLETED"
        assert status["total_records"] == 100
        assert status["successful_records"] == 95
        assert status["insert_status"] == "success"

    # region test_get_run_records [TYPE Function]
    # @PURPOSE: get_run_records returns paginated records.
    def test_get_run_records(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        record = MagicMock(spec=TranslationRecord)
        record.id = "rec-1"
        record.batch_id = "batch-1"
        record.run_id = "run-1"
        record.source_sql = "SELECT 1"
        record.target_sql = "SELECT 2"
        record.source_object_type = "table_row"
        record.source_object_id = "0"
        record.source_object_name = ""
        record.status = "SUCCESS"
        record.error_message = None
        record.created_at = datetime.now(UTC)
        record.languages = []

        db.query.return_value.options.return_value.filter.return_value.count.return_value = 1
        db.query.return_value.options.return_value.filter.return_value.order_by.return_value.offset.return_value.limit.return_value.all.return_value = [record]

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        result = orch.get_run_records("run-1")

        assert result["total"] == 1
        assert len(result["items"]) == 1
        assert result["items"][0]["status"] == "SUCCESS"

    # region test_get_run_history [TYPE Function]
    # @PURPOSE: get_run_history returns list of runs.
    def test_get_run_history(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        run = MagicMock(spec=TranslationRun)
        run.id = "run-1"
        run.job_id = "job-123"
        run.status = "COMPLETED"
        run.started_at = datetime.now(UTC)
        run.completed_at = datetime.now(UTC)
        run.error_message = None
        run.total_records = 100
        run.successful_records = 95
        run.failed_records = 3
        run.skipped_records = 2
        run.insert_status = "success"
        run.superset_execution_id = "query-1"
        run.created_by = "test-user"
        run.created_at = datetime.now(UTC)

        db.query.return_value.filter.return_value.count.return_value = 1
        db.query.return_value.filter.return_value.order_by.return_value.offset.return_value.limit.return_value.all.return_value = [run]

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        total, runs = orch.get_run_history("job-123")

        assert total == 1
        assert len(runs) == 1
        assert runs[0]["id"] == "run-1"

    # endregion test_start_run_success
    # endregion test_start_run_missing_preview_raises
    # endregion test_start_run_draft_job_raises
    # endregion test_execute_run_invalid_status
    # endregion test_cancel_run
    # endregion test_cancel_run_invalid_status
    # endregion test_retry_failed_batches_no_failures
    # endregion test_get_run_status
    # endregion test_get_run_records
    # endregion test_get_run_history
# endregion TestTranslationOrchestrator


# region TestTranslationExecutorMultiLang [TYPE Class]
# @PURPOSE: Tests for multi-language LLM translation execution: parsing, per-language entries, source-as-reference, backward compat.
class TestTranslationExecutorMultiLang:

    # region test_parse_multi_language_response [TYPE Function]
    # @PURPOSE: _parse_llm_response handles multi-language JSON format correctly.
    def test_parse_multi_language_response(self) -> None:
        response = json.dumps({
            "rows": [
                {"row_id": 0, "detected_source_language": "fr", "ru": "текст", "en": "text", "de": "Text"},
                {"row_id": 1, "detected_source_language": "en", "ru": "дашборд", "en": "dashboard", "de": "Dashboard"},
            ]
        })
        result = TranslationExecutor._parse_llm_response(
            response, expected_count=2, target_languages=["ru", "en", "de"]
        )
        assert len(result) == 2
        assert "0" in result
        assert result["0"]["detected_source_language"] == "fr"
        assert result["0"]["ru"] == "текст"
        assert result["0"]["en"] == "text"
        assert result["0"]["de"] == "Text"
        assert "1" in result
        assert result["1"]["en"] == "dashboard"
        assert result["1"]["ru"] == "дашборд"
    # endregion test_parse_multi_language_response

    # region test_parse_legacy_format_backward_compat [TYPE Function]
    # @PURPOSE: _parse_llm_response still handles legacy single "translation" key format.
    def test_parse_legacy_format_backward_compat(self) -> None:
        response = json.dumps({
            "rows": [
                {"row_id": 0, "translation": "текст", "detected_source_language": "fr"},
                {"row_id": 1, "translation": "дашборд", "detected_source_language": "en"},
            ]
        })
        result = TranslationExecutor._parse_llm_response(
            response, expected_count=2, target_languages=["ru"]
        )
        assert len(result) == 2
        assert result["0"]["translation"] == "текст"
        assert result["1"]["detected_source_language"] == "en"
    # endregion test_parse_legacy_format_backward_compat

    # region test_multi_language_translation_language_entries [TYPE Function]
    # @PURPOSE: Multi-language LLM response creates per-language TranslationLanguage entries.
    def test_multi_language_translation_language_entries(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Mock job with multi-language target_languages
        job = MagicMock(spec=TranslationJob)
        job.id = "job-ml-1"
        job.target_languages = ["ru", "en"]
        job.target_dialect = "postgresql"
        job.source_dialect = "postgresql"
        job.translation_column = "name"
        job.provider_id = "provider-1"
        job.batch_size = 50

        executor = TranslationExecutor(db, config_manager, "test-user")

        # Patch call_openai_compatible to return multi-language response
        multi_lang_response = json.dumps({
            "rows": [
                {"row_id": "0", "detected_source_language": "fr", "ru": "текст", "en": "text"},
                {"row_id": "1", "detected_source_language": "en", "ru": "дашборд", "en": "dashboard"},
            ]
        })
        batch_rows = [
            {"row_index": "0", "source_text": "texte", "source_object_name": "Row 0"},
            {"row_index": "1", "source_text": "dashboard", "source_object_name": "Row 1"},
        ]

        with patch.object(LLMTranslationService, 'call_llm',
                   return_value=(multi_lang_response, 'stop')):
            result = executor._call_llm_for_batch(
                job=job,
                run_id="run-ml-1",
                batch_rows=batch_rows,
                dict_matches=[],
                batch_id="batch-ml-1",
            )

        assert result["successful"] == 2
        assert result["failed"] == 0
        assert result["skipped"] == 0

        # Check that TranslationLanguage entries were created for each row per language
        # Row 0: detected_source=fr, target_languages=[ru,en] → ru and en both create entries = 2
        # Row 1: detected_source=en, target_languages=[ru,en] → en skipped (matches source), ru creates entry = 1
        # Total: 3 entries (source-matched languages are now skipped)
        lang_added_calls = [
            call for call in db.add.call_args_list
            if isinstance(call[0][0], TranslationLanguage)
        ]
        assert len(lang_added_calls) >= 3, f"Expected at least 3 TranslationLanguage entries, got {len(lang_added_calls)}"

        # Group by language code
        lang_codes: list[str] = []
        for call in lang_added_calls:
            lang_entry = call[0][0]
            lang_codes.append(lang_entry.language_code)
        assert "ru" in lang_codes
        assert "en" in lang_codes
        # ru should appear twice (once per row), en only once (skipped for row 1 where source=en)
        assert lang_codes.count("ru") == 2
        assert lang_codes.count("en") == 1
    # endregion test_multi_language_translation_language_entries

    # region test_source_as_reference [TYPE Function]
    # @PURPOSE: When detected source language matches a target language, store original text verbatim.
    def test_source_as_reference(self) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        job = MagicMock(spec=TranslationJob)
        job.id = "job-sar-1"
        job.target_languages = ["fr", "en"]
        job.target_dialect = "postgresql"
        job.source_dialect = "postgresql"
        job.translation_column = "name"
        job.provider_id = "provider-1"
        job.batch_size = 50

        executor = TranslationExecutor(db, config_manager, "test-user")

        # Source is French, target_languages include French
        response = json.dumps({
            "rows": [
                {"row_id": "0", "detected_source_language": "fr", "fr": "texte français", "en": "french text"},
            ]
        })
        batch_rows = [
            {"row_index": "0", "source_text": "texte français", "source_object_name": "Row 0"},
        ]

        with patch.object(LLMTranslationService, 'call_llm',
                   return_value=(response, 'stop')):
            result = executor._call_llm_for_batch(
                job=job,
                run_id="run-sar-1",
                batch_rows=batch_rows,
                dict_matches=[],
                batch_id="batch-sar-1",
            )

        assert result["successful"] == 1

        # Find the TranslationLanguage for French and English
        # Detected source is "fr" → fr entry is skipped (matches source)
        # Only "en" should have a TranslationLanguage entry
        lang_calls = [
            call for call in db.add.call_args_list
            if isinstance(call[0][0], TranslationLanguage)
        ]
        fr_entry = None
        en_entry = None
        for call in lang_calls:
            le = call[0][0]
            if le.language_code == "fr":
                fr_entry = le
            elif le.language_code == "en":
                en_entry = le

        assert fr_entry is None, "FR language entry should NOT exist (detected source is fr, now skipped)"
        assert en_entry is not None, "EN language entry should exist"
        # EN should get the LLM-translated value
        assert en_entry.translated_value == "french text"
    # endregion test_source_as_reference

    # region test_per_language_stats_on_execute_run [TYPE Function]
    # @PURPOSE: execute_run with multi-language job creates and populates TranslationRunLanguageStats.
    def test_per_language_stats_on_execute_run(self, mock_job: MagicMock) -> None:
        db = MagicMock()
        config_manager = MagicMock()

        # Configure mock_job for multi-language
        mock_job.target_languages = ["ru", "en"]
        mock_job.target_table = "target_tbl"

        # Mock run
        run = MagicMock()
        run.id = "run-stats-1"
        run.job_id = "job-123"
        run.status = "PENDING"

        # Mock completed run returned by executor
        completed_run = MagicMock()
        completed_run.id = "run-stats-1"
        completed_run.job_id = "job-123"
        completed_run.status = "COMPLETED"
        completed_run.total_records = 5
        completed_run.successful_records = 5
        completed_run.failed_records = 0
        completed_run.skipped_records = 0
        completed_run.completed_at = datetime.now(UTC)
        completed_run.error_message = None
        completed_run.insert_status = "success"
        completed_run.superset_execution_id = "q-1"
        completed_run.superset_execution_log = {}

        # Mock queries: job -> mock_job, run -> completed_run
        db.query.return_value.filter.return_value.first.side_effect = [
            mock_job,
            completed_run,
        ]

        orch = TranslationOrchestrator(db, config_manager, "test-user")
        engine = orch._runner._executor_engine
        with patch.object(engine, 'event_log'), \
             patch.object(engine._sql_service, 'generate_and_insert_sql',
                          return_value={"status": "success", "query_id": "q-1", "rows_affected": 5}), \
             patch.object(engine._aggregator, 'update_language_stats') as mock_update_stats, \
             patch('src.plugins.translate.orchestrator_exec.TranslationExecutor') as MockExecutor:

            mock_executor_instance = MagicMock()
            mock_executor_instance.execute_run.return_value = completed_run
            MockExecutor.return_value = mock_executor_instance

            result = orch.execute_run(run)

        assert result.status == "COMPLETED"

        # Verify that TranslationRunLanguageStats entries were created
        stats_added = [
            call for call in db.add.call_args_list
            if isinstance(call[0][0], TranslationRunLanguageStats)
        ]
        # 2 language stats entries (ru, en)
        assert len(stats_added) >= 2, (
            f"Expected at least 2 TranslationRunLanguageStats entries, got {len(stats_added)}"
        )
        codes = [call[0][0].language_code for call in stats_added]
        assert "ru" in codes
        assert "en" in codes

        # Verify update_language_stats was called
        mock_update_stats.assert_called_once()
    # endregion test_per_language_stats_on_execute_run

# endregion TestTranslationExecutorMultiLang
# endregion OrchestratorTests
