# #region Test.OrchestratorSqlRows [C:3] [TYPE Module] [SEMANTICS test,translate,sql,rows,columns]
# @BRIEF Direct unit tests for orchestrator_sql_rows.py — build_columns, build_context_keys, build_rows.
# @RELATION BINDS_TO -> [orchestrator_sql_rows]
# @TEST_EDGE: build_columns_target_key_cols_effective_target -> covers lines with extension ordering
# @TEST_EDGE: build_context_keys_translation_column -> covers line 46 when translation_column != effective_target
# @TEST_EDGE: build_rows_languages_populated -> covers lines 69, 96-106 (translation language loop)
# @TEST_EDGE: build_rows_key_col_none -> covers line 80 (else branch for None source value)
# @TEST_EDGE: build_rows_skip_src_language -> covers line 97-98 (skip lang matching detected source)
# @TEST_EDGE: build_rows_no_languages_fallback -> covers lines 107-114 (no languages fallback)

from __future__ import annotations

from unittest.mock import MagicMock

import pytest

from src.plugins.translate.orchestrator_sql_rows import (
    build_columns,
    build_context_keys,
    build_rows,
)


# ── Fixtures ──


@pytest.fixture
def mock_job():
    job = MagicMock()
    job.target_key_cols = ["product_id", "store_id"]
    job.target_language_column = "lang"
    job.target_source_column = "source_text"
    job.target_source_language_column = "src_lang"
    job.context_columns = ["category", "brand"]
    job.translation_column = "name"
    return job


@pytest.fixture
def mock_language_entry_ru():
    lang = MagicMock()
    lang.language_code = "ru"
    lang.source_language_detected = None
    lang.final_value = "Привет"
    lang.translated_value = "Привет (auto)"
    return lang


@pytest.fixture
def mock_language_entry_de():
    lang = MagicMock()
    lang.language_code = "de"
    lang.source_language_detected = None
    lang.final_value = "Hallo"
    lang.translated_value = None
    return lang


@pytest.fixture
def mock_language_entry_src():
    """Language entry matching detected source language — should be skipped."""
    lang = MagicMock()
    lang.language_code = "en"
    lang.source_language_detected = "en"
    lang.final_value = "Hello"
    return lang


@pytest.fixture
def mock_record_with_languages(source_language_detected: str | None = "en"):
    rec = MagicMock()
    rec.source_data = {
        "product_id": 123,
        "store_id": "NYC-01",
        "category": "greeting",
        "brand": "Acme",
    }
    rec.source_sql = "SELECT 'Hello'"
    rec.target_sql = "SELECT 'Hello'"
    return rec


@pytest.fixture
def mock_record_key_col_none():
    """Record where one key column value is None — tests line 80 else branch."""
    rec = MagicMock()
    rec.source_data = {
        "product_id": 456,
        "store_id": None,  # ← key column with None value
        "category": "farewell",
    }
    rec.source_sql = "SELECT 'Goodbye'"
    rec.target_sql = "SELECT 'Goodbye'"
    rec.languages = []
    return rec


@pytest.fixture
def mock_record_no_languages():
    """Record with empty languages list — tests the fallback path (lines 107-114)."""
    rec = MagicMock()
    rec.source_data = {
        "product_id": 789,
        "category": "test",
    }
    rec.source_sql = "SELECT 'Test'"
    rec.target_sql = "SELECT 'Test'"
    rec.languages = []
    return rec


# ── build_columns ──


class TestBuildColumns:
    """build_columns — column list construction."""

    def test_basic_columns(self, mock_job):
        """Job with target_key_cols and effective_target returns combined list."""
        cols = build_columns(mock_job, effective_target="translated_name")
        assert "product_id" in cols
        assert "store_id" in cols
        assert "translated_name" in cols
        assert "lang" in cols
        assert "source_text" in cols
        assert "src_lang" in cols
        assert "context" in cols
        assert "is_original" in cols

    def test_no_key_cols(self, mock_job):
        """Job with empty target_key_cols returns minimal columns."""
        mock_job.target_key_cols = []
        cols = build_columns(mock_job, effective_target="translated_name")
        assert "product_id" not in cols
        assert "translated_name" in cols

    def test_no_effective_target(self, mock_job):
        """When effective_target is None, omit target column."""
        cols = build_columns(mock_job, effective_target=None)
        assert "translated_name" not in cols
        assert "product_id" in cols

    def test_dedup_same_column(self, mock_job):
        """Dedup: same column appearing twice is only included once."""
        mock_job.target_key_cols = ["product_id", "product_id"]
        cols = build_columns(mock_job, effective_target="product_id")
        product_count = sum(1 for c in cols if c == "product_id")
        assert product_count == 1

    def test_empty_column_skipped(self, mock_job):
        """Empty string or None columns are skipped during dedup."""
        mock_job.target_key_cols = ["", None, "valid"]
        cols = build_columns(mock_job, effective_target="valid")
        assert "valid" in cols


# ── build_context_keys ──


class TestBuildContextKeys:
    """build_context_keys — context key list construction."""

    def test_basic_context_keys(self, mock_job):
        """Job context_columns plus translation_column (when != target)."""
        keys = build_context_keys(mock_job, effective_target="translated_name")
        assert "category" in keys
        assert "brand" in keys
        assert "name" in keys  # translation_column added because != translated_name

    def test_translation_column_equals_target(self, mock_job):
        """When translation_column == effective_target, don't add it again."""
        keys = build_context_keys(mock_job, effective_target="name")
        assert "category" in keys
        assert "brand" in keys
        # translation_column == effective_target → condition `if translation_column != effective_target` is False
        assert "name" not in keys

    def test_no_context_columns(self, mock_job):
        """When context_columns is None, still add translation_column if needed."""
        mock_job.context_columns = None
        keys = build_context_keys(mock_job, effective_target="translated_name")
        assert "name" in keys  # translation_column added


# ── build_rows ──


class TestBuildRows:
    """build_rows — row data construction with language expansion."""

    def _make_lang(
        self,
        code: str,
        final: str | None = None,
        translated: str | None = None,
        src_lang: str | None = None,
    ):
        lang = MagicMock()
        lang.language_code = code
        lang.final_value = final
        lang.translated_value = translated
        lang.source_language_detected = src_lang
        return lang

    def _make_record(self, source_data: dict | None = None, source_sql: str = "", target_sql: str = "", languages: list | None = None):
        rec = MagicMock()
        rec.source_data = source_data or {}
        rec.source_sql = source_sql
        rec.target_sql = target_sql
        rec.languages = languages or []
        return rec

    # ── Line 69: detected_src_lang from record languages ──

    def test_detected_src_lang_from_languages(self, mock_job):
        """When record has languages, detected_src_lang comes from first language entry."""
        lang = self._make_lang("en", final="X", src_lang="es")
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT 1",
            languages=[lang],
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        assert len(rows) >= 1
        # The original row should have src_lang = "es" (detected)
        original = [r for r in rows if r["is_original"] == 1][0]
        assert original["src_lang"] == "es"

    def test_detected_src_lang_fallback_to_und(self, mock_job):
        """When language entry has no source_language_detected, fallback to 'und'."""
        lang = self._make_lang("en", final="X", src_lang=None)
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT 1",
            languages=[lang],
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        original = [r for r in rows if r["is_original"] == 1][0]
        assert original["src_lang"] == "und"

    # ── Line 80: key column with None value ──

    def test_key_col_with_none_value(self, mock_job):
        """When a key column has None in source_data, base_row[k] is set to None."""
        rec = self._make_record(
            source_data={"product_id": 1, "store_id": None},
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        # store_id is a key column → base_row["store_id"] should be None
        original = [r for r in rows if r["is_original"] == 1][0]
        assert original["store_id"] is None
        assert original["product_id"] == 1

    # ── Lines 96-106: translation language iteration ──

    def test_translation_loop_creates_translated_rows(self, mock_job):
        """For each language (except detected source), a translated row is created."""
        lang_ru = self._make_lang("ru", final="Привет")
        lang_de = self._make_lang("de", final="Hallo")
        rec = self._make_record(
            source_data={"product_id": 1, "store_id": "S1"},
            source_sql="SELECT 'Hello'",
            languages=[lang_ru, lang_de],
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        # 1 original + 2 translations = 3 rows
        assert len(rows) == 3
        originals = [r for r in rows if r["is_original"] == 1]
        translations = [r for r in rows if r["is_original"] == 0]
        assert len(originals) == 1
        assert len(translations) == 2

        ru_row = next(r for r in translations if r["lang"] == "ru")
        assert ru_row["translated_name"] == "Привет"  # final_value used
        de_row = next(r for r in translations if r["lang"] == "de")
        assert de_row["translated_name"] == "Hallo"

    # ── Line 97-98: skip language matching detected source ──

    def test_skip_src_language_in_translation_loop(self, mock_job):
        """Language entry matching detected_src_lang is skipped."""
        detected_en = self._make_lang("en", final="Hello", src_lang="en")
        lang_ru = self._make_lang("ru", final="Привет")
        rec = self._make_record(
            source_data={"product_id": 1, "store_id": "S1"},
            source_sql="SELECT 'Hello'",
            languages=[detected_en, lang_ru],
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        # 1 original + 1 translation (ru only, en skipped) = 2 rows
        assert len(rows) == 2
        translated_langs = [r["lang"] for r in rows if r["is_original"] == 0]
        assert translated_langs == ["ru"]

    # ── Line 100: final_value vs translated_value fallback ──

    def test_translation_uses_final_value_then_translated_value(self, mock_job):
        """trans_value = lang.final_value or lang.translated_value or ''"""
        lang_final = self._make_lang("de", final="Hallo", translated="Hallo (auto)")
        lang_translated = self._make_lang("fr", final=None, translated="Bonjour")
        lang_both_none = self._make_lang("it", final=None, translated=None)
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT 'Hello'",
            languages=[lang_final, lang_translated, lang_both_none],
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        translations = {r["lang"]: r["translated_name"] for r in rows if r["is_original"] == 0}
        # final_value takes precedence
        assert translations["de"] == "Hallo"
        # translated_value fallback
        assert translations["fr"] == "Bonjour"
        # empty fallback
        assert translations["it"] == ""

    # ── Lines 107-114: no languages fallback ──

    def test_no_languages_fallback(self, mock_job):
        """When rec.languages is empty, fallback row uses target_sql and primary_language."""
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT 'Test'",
            target_sql="SELECT 'Test'",
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="fr",
            context_keys=[],
        )
        assert len(rows) == 2  # 1 original + 1 fallback
        fallback = [r for r in rows if r["is_original"] == 0][0]
        assert fallback["translated_name"] == "SELECT 'Test'"
        assert fallback["lang"] == "fr"

    def test_no_languages_no_target_column(self, mock_job):
        """Fallback when effective_target is None — no target column in fallback."""
        mock_job.target_language_column = "lang"
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT 'Test'",
            target_sql="SELECT 'Test'",
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target=None,
            primary_language="fr",
            context_keys=[],
        )
        assert len(rows) == 2
        fallback = [r for r in rows if r["is_original"] == 0][0]
        # No translated_name column since effective_target is None
        assert "translated_name" not in fallback
        assert fallback["lang"] == "fr"

    # ── Context data ──

    def test_context_data_in_rows(self, mock_job):
        """Context keys are serialized into context JSON."""
        rec = self._make_record(
            source_data={"product_id": 1, "store_id": "S1", "category": "greeting"},
            source_sql="SELECT 1",
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=["category"],
        )
        import json
        original = [r for r in rows if r["is_original"] == 1][0]
        context = json.loads(original["context"])
        assert context["category"] == "greeting"

    # ── target_source_column ──

    def test_target_source_column_in_row(self, mock_job):
        """When job has target_source_column, source_sql is included."""
        rec = self._make_record(
            source_data={"product_id": 1},
            source_sql="SELECT original",
        )
        rows = build_rows(
            records=[rec],
            job=mock_job,
            effective_target="translated_name",
            primary_language="en",
            context_keys=[],
        )
        original = [r for r in rows if r["is_original"] == 1][0]
        assert original["source_text"] == "SELECT original"
# #endregion Test.OrchestratorSqlRows
