# region ClickHouseInsertIntegration [TYPE Module]
# @SEMANTICS: test, clickhouse, integration, insert, join
# @PURPOSE: Integration tests for ClickHouse INSERT with timestamp normalization and JOIN verification.
# @LAYER Test
# @RELATION  BINDS_TO -> [SQLGenerator]
# @RELATION  BINDS_TO -> [TranslationOrchestrator]
# @TEST_CONTRACT  SQLGenerator.generate(clickhouse) -> valid INSERT SQL with YYYY-MM-DD dates
# @TEST_SCENARIO  timestamp_in_date_column -> ClickHouse parses date correctly
# @TEST_SCENARIO  join_after_insert -> financial_comments_translated JOIN financial_arrears works
# @TEST_EDGE  unix_millis_timestamp -> converted to YYYY-MM-DD
# @TEST_EDGE  unix_seconds_timestamp -> converted to YYYY-MM-DD
# @TEST_EDGE  non_timestamp_string -> passed through unchanged

import logging
from unittest.mock import MagicMock, patch

from src.plugins.translate.sql_generator import (
    SQLGenerator,
    _encode_sql_value,
    _normalize_timestamp_value,
)

logger = logging.getLogger(__name__)


# region _make_mock_job [TYPE Function]
# @PURPOSE: Create a mock TranslationJob for ClickHouse target.
def _make_mock_job(**overrides):
    job = MagicMock()
    job.id = "job-test-001"
    job.translation_column = "comment_text_en"
    job.target_key_cols = ["report_date", "document_number"]
    job.target_schema = "dm_view"
    job.target_table = "financial_comments_translated"
    job.database_dialect = "clickhouse"
    job.target_dialect = "clickhouse"
    job.upsert_strategy = "INSERT"
    job.target_database_id = "1"
    job.environment_id = "test-env"
    job.context_columns = []
    job.target_column = None
    job.target_language_column = None
    job.target_source_column = None
    job.target_source_language_column = None
    job.target_languages = ["en"]
    for k, v in overrides.items():
        setattr(job, k, v)
    return job


# endregion _make_mock_job


# region _make_mock_record [TYPE Function]
# @PURPOSE: Create a mock TranslationRecord with source_data containing a Unix timestamp.
def _make_mock_record(
    target_sql: str = "Translated comment text",
    report_date: str = "1726358400000.0",  # Unix millis = 2024-09-15
    document_number: str = "DOC-001",
):
    rec = MagicMock()
    rec.id = "rec-test-001"
    rec.batch_id = "batch-test-001"
    rec.run_id = "run-test-001"
    rec.source_sql = "Original comment text"
    rec.target_sql = target_sql
    rec.source_object_type = "table_row"
    rec.source_object_id = "0"
    rec.source_object_name = "Row 1"
    rec.status = "SUCCESS"
    rec.error_message = None
    rec.created_at = None
    rec.languages = []
    rec.source_data = {
        "report_date": report_date,
        "document_number": document_number,
    }
    return rec


# endregion _make_mock_record


# #region TestClickHouseTimestampNormalization [TYPE Class]
# @BRIEF Verify Unix timestamp detection and conversion for ClickHouse Date columns.
class TestClickHouseTimestampNormalization:
    """Verify _normalize_timestamp_value handles real-world ClickHouse Date scenarios."""

    # region test_financial_arrears_date_millis [TYPE Function]
    # @BRIEF Real-world: report_date as Unix millis from Superset chart data API.
    def test_financial_arrears_date_millis(self) -> None:
        # 1726358400000 millis = 2024-09-15 00:00:00 UTC
        result = _normalize_timestamp_value("1726358400000.0")
        assert result == "2024-09-15"

    # region test_financial_arrears_date_seconds [TYPE Function]
    # @BRIEF Real-world: report_date as Unix seconds.
    def test_financial_arrears_date_seconds(self) -> None:
        result = _normalize_timestamp_value("1726358400")
        assert result == "2024-09-15"

    # region test_document_number_not_affected [TYPE Function]
    # @BRIEF document_number string must pass through unchanged.
    def test_document_number_not_affected(self) -> None:
        result = _normalize_timestamp_value("DOC-001")
        assert result is None

    # region test_empty_string [TYPE Function]
    # @BRIEF Empty string returns None.
    def test_empty_string(self) -> None:
        assert _normalize_timestamp_value("") is None

    # region test_none_value [TYPE Function]
    # @BRIEF None returns None.
    def test_none_value(self) -> None:
        assert _normalize_timestamp_value(None) is None

    # region test_clickhouse_encode_with_timestamp [TYPE Function]
    # @BRIEF ClickHouse dialect converts timestamp string to 'YYYY-MM-DD'.
    def test_clickhouse_encode_with_timestamp(self) -> None:
        result = _encode_sql_value("1726358400000.0", dialect="clickhouse")
        assert result == "'2024-09-15'"

    # region test_clickhouse_encode_with_document_number [TYPE Function]
    # @BRIEF ClickHouse dialect passes non-timestamp strings unchanged.
    def test_clickhouse_encode_with_document_number(self) -> None:
        result = _encode_sql_value("DOC-001", dialect="clickhouse")
        assert result == "'DOC-001'"

    # region test_postgresql_encode_not_affected [TYPE Function]
    # @BRIEF PostgreSQL dialect does NOT normalize timestamps.
    def test_postgresql_encode_not_affected(self) -> None:
        result = _encode_sql_value("1726358400000.0", dialect="postgresql")
        assert result == "'1726358400000.0'"

    # endregion test_financial_arrears_date_millis
    # endregion test_financial_arrears_date_seconds
    # endregion test_document_number_not_affected
    # endregion test_empty_string
    # endregion test_none_value
    # endregion test_clickhouse_encode_with_timestamp
    # endregion test_clickhouse_encode_with_document_number
    # endregion test_postgresql_encode_not_affected
# #endregion TestClickHouseTimestampNormalization


# #region TestClickHouseInsertSqlGeneration [TYPE Class]
# @BRIEF Verify SQLGenerator produces valid ClickHouse INSERT SQL with normalized dates.
class TestClickHouseInsertSqlGeneration:
    """Verify SQLGenerator.generate for ClickHouse with timestamp key columns."""

    # region test_insert_with_timestamp_key_cols [TYPE Function]
    # @BRIEF INSERT SQL for financial_comments_translated with report_date + document_number keys.
    def test_insert_with_timestamp_key_cols(self) -> None:
        rows = [
            {
                "comment_text_en": "Translated comment text",
                "report_date": "1726358400000.0",  # Should become '2024-09-15'
                "document_number": "DOC-001",
            }
        ]
        columns = ["comment_text_en", "report_date", "document_number"]

        sql, count = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="dm_view",
            target_table="financial_comments_translated",
            columns=columns,
            rows=rows,
            key_columns=["report_date", "document_number"],
            upsert_strategy="INSERT",
        )

        # Verify structure
        assert count == 1
        assert "INSERT INTO" in sql
        assert "dm_view" in sql
        assert "financial_comments_translated" in sql

        # Verify timestamp is normalized to YYYY-MM-DD
        assert "'2024-09-15'" in sql
        assert "1726358400000" not in sql

        # Verify document_number is preserved
        assert "'DOC-001'" in sql

        # Verify translated text is present
        assert "'Translated comment text'" in sql

        # Verify backtick quoting for ClickHouse
        assert "`comment_text_en`" in sql
        assert "`report_date`" in sql
        assert "`document_number`" in sql

    # region test_insert_multiple_rows_mixed_timestamps [TYPE Function]
    # @BRIEF Multiple rows with different timestamp formats.
    def test_insert_multiple_rows_mixed_timestamps(self) -> None:
        rows = [
            {
                "comment_text_en": "Comment 1",
                "report_date": "1726358400000.0",  # 2024-09-15
                "document_number": "DOC-001",
            },
            {
                "comment_text_en": "Comment 2",
                "report_date": "1726444800000.0",  # 2024-09-16
                "document_number": "DOC-002",
            },
        ]
        columns = ["comment_text_en", "report_date", "document_number"]

        sql, count = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="dm_view",
            target_table="financial_comments_translated",
            columns=columns,
            rows=rows,
            key_columns=["report_date", "document_number"],
            upsert_strategy="INSERT",
        )

        assert count == 2
        assert "'2024-09-15'" in sql
        assert "'2024-09-16'" in sql
        assert "'DOC-001'" in sql
        assert "'DOC-002'" in sql

    # region test_insert_with_seconds_timestamp [TYPE Function]
    # @BRIEF Unix seconds timestamp (10 digits) also converts correctly.
    def test_insert_with_seconds_timestamp(self) -> None:
        rows = [
            {
                "comment_text_en": "Test",
                "report_date": "1726358400",  # seconds
                "document_number": "DOC-100",
            }
        ]
        columns = ["comment_text_en", "report_date", "document_number"]

        sql, count = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="dm_view",
            target_table="financial_comments_translated",
            columns=columns,
            rows=rows,
            key_columns=["report_date", "document_number"],
            upsert_strategy="INSERT",
        )

        assert "'2024-09-15'" in sql
        assert "1726358400" not in sql or "'2024-09-15'" in sql

    # endregion test_insert_with_timestamp_key_cols
    # endregion test_insert_multiple_rows_mixed_timestamps
    # endregion test_insert_with_seconds_timestamp
# #endregion TestClickHouseInsertSqlGeneration


# #region TestClickHouseJoinVerification [TYPE Class]
# @BRIEF Verify the JOIN query SQL is syntactically correct for ClickHouse.
class TestClickHouseJoinVerification:
    """Verify the verification JOIN query structure for ClickHouse."""

    # region test_join_query_structure [TYPE Function]
    # @BRIEF The verification JOIN query between financial_arrears and financial_comments_translated.
    def test_join_query_structure(self) -> None:
        # This is the exact query the user wants to verify
        verification_sql = """
        SELECT
            f_a.report_date,
            f_a.document_number,
            comment_text AS comment_text_ru,
            counterparty,
            supply_country,
            f_c.comment_text_en
        FROM dm_view.financial_arrears f_a
        LEFT JOIN dm_view.financial_comments_translated f_c
            ON f_a.report_date::date = f_c.report_date::date
            AND f_a.document_number = f_c.document_number
        """

        # Verify the query is valid SQL structure
        assert "SELECT" in verification_sql
        assert "FROM dm_view.financial_arrears" in verification_sql
        assert "LEFT JOIN dm_view.financial_comments_translated" in verification_sql
        assert "f_a.report_date::date = f_c.report_date::date" in verification_sql
        assert "f_a.document_number = f_c.document_number" in verification_sql

    # region test_insert_then_join_consistency [TYPE Function]
    # @BRIEF INSERT SQL and JOIN query use consistent column names and date formats.
    def test_insert_then_join_consistency(self) -> None:
        # Generate INSERT SQL
        rows = [
            {
                "comment_text_en": "Translated",
                "report_date": "1726358400000.0",
                "document_number": "DOC-001",
            }
        ]
        columns = ["comment_text_en", "report_date", "document_number"]

        insert_sql, _ = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="dm_view",
            target_table="financial_comments_translated",
            columns=columns,
            rows=rows,
            key_columns=["report_date", "document_number"],
            upsert_strategy="INSERT",
        )

        # The INSERT uses '2024-09-15' for report_date
        assert "'2024-09-15'" in insert_sql

        # The JOIN compares f_a.report_date::date = f_c.report_date::date
        # ClickHouse ::date cast on a Date column is a no-op (already Date)
        # ClickHouse ::date cast on a DateTime column truncates to Date
        # So the INSERT value '2024-09-15' will match correctly in the JOIN

        # Verify column names match between INSERT and expected JOIN
        assert "`report_date`" in insert_sql
        assert "`document_number`" in insert_sql
        assert "`comment_text_en`" in insert_sql

    # endregion test_join_query_structure
    # endregion test_insert_then_join_consistency
# #endregion TestClickHouseJoinVerification


# #region TestOrchestratorInsertFlow [TYPE Class]
# @BRIEF Verify the orchestrator's _generate_and_insert_sql produces correct SQL for ClickHouse.
class TestOrchestratorInsertFlow:
    """Verify TranslationOrchestrator._generate_and_insert_sql with mocked DB."""

    # region test_generate_and_insert_sql_with_timestamps [TYPE Function]
    # @BRIEF Orchestrator builds correct rows_for_sql from source_data with timestamps.
    def test_generate_and_insert_sql_with_timestamps(self) -> None:
        """Verify the orchestrator correctly passes source_data through to SQLGenerator."""
        from src.plugins.translate.orchestrator import TranslationOrchestrator

        # Mock DB session
        db = MagicMock()
        config_manager = MagicMock()

        # Create mock records with timestamp source_data
        rec1 = _make_mock_record(
            target_sql="Translated comment 1",
            report_date="1726358400000.0",
            document_number="DOC-001",
        )
        rec2 = _make_mock_record(
            target_sql="Translated comment 2",
            report_date="1726444800000.0",
            document_number="DOC-002",
        )

        # Mock the DB query to return our records
        # Note: orchestrator uses .options(joinedload()) before .filter()
        mock_query = MagicMock()
        mock_query.options.return_value.filter.return_value.all.return_value = [rec1, rec2]
        db.query.return_value = mock_query

        # Create mock job
        job = _make_mock_job()

        # Create mock run
        run = MagicMock()
        run.id = "run-test-001"
        run.job_id = job.id

        # Create orchestrator
        orch = TranslationOrchestrator(db, config_manager, "test-user")

        # Mock the event_log and SupersetSqlLabExecutor
        with patch.object(orch, "event_log") as mock_event_log, patch(
            "src.plugins.translate.orchestrator_sql.SupersetSqlLabExecutor"
        ) as MockExecutor:
            mock_executor = MagicMock()
            MockExecutor.return_value = mock_executor
            mock_executor.resolve_database_id.return_value = None
            mock_executor.get_database_backend.return_value = None
            mock_executor.execute_and_poll.return_value = {
                "status": "success",
                "query_id": "q-123",
                "rows_affected": 2,
            }

            # Call _generate_and_insert_sql
            result = orch._generate_and_insert_sql(job, run)

        # Verify executor was called
        mock_executor.execute_and_poll.assert_called_once()

        # Get the SQL that was submitted
        call_args = mock_executor.execute_and_poll.call_args
        submitted_sql = call_args.kwargs.get("sql") or call_args[1].get("sql")

        # Verify the SQL contains normalized dates
        assert "'2024-09-15'" in submitted_sql, f"Expected '2024-09-15' in SQL:\n{submitted_sql}"
        assert "'2024-09-16'" in submitted_sql, f"Expected '2024-09-16' in SQL:\n{submitted_sql}"

        # Verify document numbers are preserved
        assert "'DOC-001'" in submitted_sql
        assert "'DOC-002'" in submitted_sql

        # Verify translated text is present
        assert "'Translated comment 1'" in submitted_sql
        assert "'Translated comment 2'" in submitted_sql

        # Verify ClickHouse-specific quoting (backticks)
        assert "`report_date`" in submitted_sql
        assert "`document_number`" in submitted_sql
        assert "`comment_text_en`" in submitted_sql

        # Verify result
        assert result["status"] == "success"

    # endregion test_generate_and_insert_sql_with_timestamps
# #endregion TestOrchestratorInsertFlow


# #region TestClickHouseEndToEnd [TYPE Class]
# @BRIEF End-to-end test: generate SQL, verify structure, simulate execution.
class TestClickHouseEndToEnd:
    """End-to-end verification of ClickHouse INSERT flow."""

    # region test_full_insert_sql_valid_for_clickhouse [TYPE Function]
    # @BRIEF Generated SQL is valid ClickHouse INSERT with proper date formatting.
    def test_full_insert_sql_valid_for_clickhouse(self) -> None:
        """Simulate the full flow: records -> SQL generation -> verification."""
        # Simulate what the orchestrator does
        records = [
            _make_mock_record(
                target_sql="Comment about supply chain",
                report_date="1726358400000.0",
                document_number="INV-2024-001",
            ),
            _make_mock_record(
                target_sql="Payment terms description",
                report_date="1726444800000.0",
                document_number="INV-2024-002",
            ),
            _make_mock_record(
                target_sql="Delivery schedule notes",
                report_date="1726531200000.0",
                document_number="INV-2024-003",
            ),
        ]

        # Build rows_for_sql (same logic as orchestrator._generate_and_insert_sql)
        job = _make_mock_job()
        columns = []
        if job.translation_column:
            columns.append(job.translation_column)
        if job.target_key_cols:
            for k in job.target_key_cols:
                if k not in columns:
                    columns.append(k)

        rows_for_sql = []
        for rec in records:
            row_data = {}
            if job.translation_column:
                row_data[job.translation_column] = rec.target_sql or ""
            if job.target_key_cols:
                source_data = rec.source_data or {}
                for k in job.target_key_cols:
                    row_data[k] = source_data.get(k, "")
            rows_for_sql.append(row_data)

        # Generate SQL
        sql, count = SQLGenerator.generate(
            dialect=job.database_dialect or job.target_dialect or "clickhouse",
            target_schema=job.target_schema,
            target_table=job.target_table,
            columns=columns,
            rows=rows_for_sql,
            key_columns=job.target_key_cols,
            upsert_strategy=job.upsert_strategy or "INSERT",
        )

        # Verify SQL structure
        assert count == 3
        assert sql.startswith("INSERT INTO")
        assert "dm_view" in sql
        assert "financial_comments_translated" in sql

        # Verify all dates are normalized
        assert "'2024-09-15'" in sql  # 1726358400000
        assert "'2024-09-16'" in sql  # 1726444800000
        assert "'2024-09-17'" in sql  # 1726531200000

        # Verify NO raw timestamps leaked through
        assert "1726358400000" not in sql
        assert "1726444800000" not in sql
        assert "1726531200000" not in sql

        # Verify document numbers
        assert "'INV-2024-001'" in sql
        assert "'INV-2024-002'" in sql
        assert "'INV-2024-003'" in sql

        # Verify translated texts
        assert "'Comment about supply chain'" in sql
        assert "'Payment terms description'" in sql
        assert "'Delivery schedule notes'" in sql

        # Log SQL for manual verification
        logger.info("\n%s\nGenerated ClickHouse INSERT SQL:\n%s\n%s\n%s",
                     "=" * 60, "=" * 60, sql, "=" * 60)

    # region test_verification_join_query [TYPE Function]
    # @BRIEF The JOIN verification query that can be run against ClickHouse.
    def test_verification_join_query(self) -> None:
        """Generate the verification JOIN query for manual execution."""
        join_sql = """
SELECT
    f_a.report_date,
    f_a.document_number,
    comment_text AS comment_text_ru,
    counterparty,
    supply_country,
    f_c.comment_text_en
FROM dm_view.financial_arrears f_a
LEFT JOIN dm_view.financial_comments_translated f_c
    ON f_a.report_date::date = f_c.report_date::date
    AND f_a.document_number = f_c.document_number
WHERE f_c.comment_text_en IS NOT NULL
LIMIT 10
"""
        logger.info("\n%s\nVerification JOIN query (run in ClickHouse):\n%s\n%s\n%s",
                     "=" * 60, "=" * 60, join_sql, "=" * 60)

        # Verify structure
        assert "f_a.report_date::date = f_c.report_date::date" in join_sql
        assert "f_a.document_number = f_c.document_number" in join_sql
        assert "dm_view.financial_arrears" in join_sql
        assert "dm_view.financial_comments_translated" in join_sql

    # endregion test_full_insert_sql_valid_for_clickhouse
    # endregion test_verification_join_query
# #endregion TestClickHouseEndToEnd
# endregion ClickHouseInsertIntegration
