# region TargetSchemaValidationTests [TYPE Module]
# @SEMANTICS: test, translate, target-schema, validation
# @PURPOSE: Tests for target table schema validation.
# @LAYER Test
# @RELATION  BINDS_TO -> [TargetSchemaValidation]
# @TEST_CONTRACT  _build_expected_columns -> list[TargetSchemaColumnInfo]
# @TEST_CONTRACT  _extract_columns_from_rows -> list[dict]
# @TEST_CONTRACT  _parse_sqllab_result -> (list[dict], bool)
# @TEST_CONTRACT  validate_target_table_schema -> TargetSchemaValidationResponse

import pytest
from unittest.mock import MagicMock, patch

from src.plugins.translate.service_target_schema import (
    _build_expected_columns,
    _extract_columns_from_rows,
    _parse_sqllab_result,
    validate_target_table_schema,
)
from src.schemas.translate import (
    TargetSchemaValidationRequest,
)


# region build_request [TYPE Function]
@pytest.fixture
def build_request():
    def _build(**overrides):
        defaults = {
            "environment_id": "env-1",
            "target_database_id": "db-1",
            "target_schema": "public",
            "target_table": "my_table",
            "target_key_cols": ["id"],
            "target_column": "translated_text",
            "translation_column": None,
            "target_language_column": None,
            "target_source_column": None,
            "target_source_language_column": None,
        }
        defaults.update(overrides)
        return TargetSchemaValidationRequest(**defaults)

    return _build


# endregion build_request


# region TestBuildExpectedColumns [TYPE Class]
class TestBuildExpectedColumns:

    def test_basic_case(self, build_request) -> None:
        req = build_request()
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert names == ["id", "translated_text", "context", "is_original"]

    def test_all_fields(self, build_request) -> None:
        req = build_request(
            target_language_column="lang",
            target_source_column="source_text",
            target_source_language_column="source_lang",
        )
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert len(names) == 7
        assert "lang" in names
        assert "source_text" in names
        assert "source_lang" in names
        assert "id" in names
        assert "translated_text" in names
        assert "context" in names
        assert "is_original" in names

    def test_empty_target_key_cols(self, build_request) -> None:
        req = build_request(target_key_cols=[])
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert names == ["translated_text", "context", "is_original"]

    def test_translation_column_fallback(self, build_request) -> None:
        req = build_request(target_column=None, translation_column="source_col")
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert "source_col" in names
        assert names[1] == "source_col"

    def test_dedup_same_names(self, build_request) -> None:
        req = build_request(
            target_key_cols=["id", "id"],
            target_column="name",
            target_language_column="name",
        )
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert names.count("id") == 1
        assert names.count("name") == 1

    def test_empty_request(self, build_request) -> None:
        req = build_request(
            target_key_cols=[],
            target_column=None,
            translation_column=None,
        )
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        assert names == ["context", "is_original"]

    def test_column_order_preserved(self, build_request) -> None:
        req = build_request(
            target_key_cols=["pk1", "pk2"],
            target_column="target_col",
            target_language_column="lang_col",
            target_source_column="src_col",
            target_source_language_column="src_lang_col",
        )
        result = _build_expected_columns(req)
        names = [c.name for c in result]
        expected_order = [
            "pk1", "pk2", "target_col", "lang_col",
            "src_col", "src_lang_col", "context", "is_original",
        ]
        assert names == expected_order
# endregion TestBuildExpectedColumns


# region TestExtractColumnsFromRows [TYPE Class]
class TestExtractColumnsFromRows:

    def test_postgres_information_schema(self) -> None:
        """PostgreSQL information_schema формат: column_name, data_type, is_nullable."""
        rows = [
            {"column_name": "id", "data_type": "integer", "is_nullable": "YES"},
            {"column_name": "name", "data_type": "varchar", "is_nullable": "NO"},
        ]
        cols = _extract_columns_from_rows(rows)
        assert len(cols) == 2
        assert cols[0]["name"] == "id"
        assert cols[0]["type"] == "integer"
        assert cols[0]["is_nullable"] is True
        assert cols[1]["name"] == "name"
        assert cols[1]["type"] == "varchar"
        assert cols[1]["is_nullable"] is False

    def test_clickhouse_describe_table(self) -> None:
        """ClickHouse DESCRIBE TABLE формат: name, type."""
        rows = [
            {"name": "report_date", "type": "Date"},
            {"name": "comment_text", "type": "String"},
            {"name": "is_original", "type": "UInt8"},
        ]
        cols = _extract_columns_from_rows(rows)
        assert len(cols) == 3
        assert cols[0]["name"] == "report_date"
        assert cols[0]["type"] == "Date"
        assert cols[2]["name"] == "is_original"
        assert cols[2]["type"] == "UInt8"

    def test_mixed_keys(self) -> None:
        """Разные ключи в одном наборе — приоритет column_name > name > Field."""
        rows = [
            {"Field": "id", "Type": "int"},
            {"name": "name", "type": "text"},
            {"column_name": "value", "data_type": "float"},
        ]
        cols = _extract_columns_from_rows(rows)
        assert len(cols) == 3
        # column_name имеет приоритет, потом name, потом Field
        assert cols[0]["name"] == "id"
        assert cols[1]["name"] == "name"
        assert cols[2]["name"] == "value"

    def test_empty_rows(self) -> None:
        cols = _extract_columns_from_rows([])
        assert cols == []

    def test_invalid_row_type(self) -> None:
        cols = _extract_columns_from_rows([123, "str", None])
        assert cols == []

    def test_row_without_name_keys(self) -> None:
        cols = _extract_columns_from_rows([{"foo": "bar"}])
        assert cols == []
# endregion TestExtractColumnsFromRows


# region TestParseSqlLabResult [TYPE Class]
class TestParseSqlLabResult:

    def test_sync_format_with_data(self) -> None:
        """Формат 1: прямой ответ с data."""
        result = {
            "raw_response": {
                "columns": [{"name": "col1"}],
                "data": [{"col1": "val1"}],
            }
        }
        rows, exists = _parse_sqllab_result(result)
        assert exists is True
        assert len(rows) == 1
        assert rows[0]["col1"] == "val1"

    def test_async_format(self) -> None:
        """Формат 2: вложенный result."""
        result = {
            "raw_response": {
                "result": {
                    "columns": [{"name": "col1"}],
                    "data": [{"col1": "val1"}],
                }
            }
        }
        rows, exists = _parse_sqllab_result(result)
        assert exists is True
        assert len(rows) == 1

    def test_get_query_results_format(self) -> None:
        """Формат 3: results → data."""
        result = {
            "raw_response": {
                "results": {
                    "columns": [{"name": "col1"}],
                    "data": [{"col1": "val1"}],
                }
            }
        }
        rows, exists = _parse_sqllab_result(result)
        assert exists is True
        assert len(rows) == 1

    def test_empty_data(self) -> None:
        result = {"raw_response": {"columns": [{"name": "c"}], "data": []}}
        rows, exists = _parse_sqllab_result(result)
        assert exists is False
        assert rows == []

    def test_no_data_key(self) -> None:
        result = {"raw_response": {"columns": [{"name": "c"}]}}
        rows, exists = _parse_sqllab_result(result)
        assert exists is False
        assert rows == []

    def test_none_result(self) -> None:
        rows, exists = _parse_sqllab_result(None)
        assert rows == []
        assert exists is False

    def test_raw_response_none(self) -> None:
        rows, exists = _parse_sqllab_result({"raw_response": None})
        assert rows == []
        assert exists is False

    def test_list_based_data(self) -> None:
        """Строки в виде списков вместо словарей (старые версии Superset)."""
        result = {
            "raw_response": {
                "columns": [{"name": "id"}, {"name": "name"}],
                "data": [[1, "foo"], [2, "bar"]],
            }
        }
        rows, exists = _parse_sqllab_result(result)
        assert exists is True
        assert len(rows) == 2
        assert rows[0]["id"] == 1
        assert rows[0]["name"] == "foo"
# endregion TestParseSqlLabResult


# region TestValidateTargetTableSchema [TYPE Class]
class TestValidateTargetTableSchema:

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_all_columns_present(self, mock_executor, build_request) -> None:
        """Все ожидаемые колонки присутствуют в таблице."""
        req = build_request()
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "postgresql"
        instance.get_database_name.return_value = "test_db"
        instance.execute_and_poll.return_value = {
            "status": "success",
            "raw_response": {
                "columns": [{"name": "column_name"}, {"name": "data_type"}],
                "data": [
                    {"column_name": "id", "data_type": "integer"},
                    {"column_name": "translated_text", "data_type": "text"},
                    {"column_name": "context", "data_type": "text"},
                    {"column_name": "is_original", "data_type": "boolean"},
                ],
            },
        }

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is True
        assert response.error is None
        assert response.all_present is True
        assert len(response.missing_columns) == 0
        assert len(response.expected_columns) == 4
        assert len(response.actual_columns) == 4
        assert response.database_name == "test_db"
        assert response.database_backend == "postgresql"

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_missing_columns(self, mock_executor, build_request) -> None:
        """В таблице не хватает некоторых ожидаемых колонок."""
        req = build_request()
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "postgresql"
        instance.get_database_name.return_value = "test_db"
        instance.execute_and_poll.return_value = {
            "status": "success",
            "raw_response": {
                "columns": [{"name": "column_name"}],
                "data": [
                    {"column_name": "id"},
                    {"column_name": "context"},
                ],
            },
        }

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is True
        assert response.all_present is False
        missing_names = {c.name for c in response.missing_columns}
        assert "translated_text" in missing_names
        assert "is_original" in missing_names
        assert "id" not in missing_names

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_clickhouse_describe_table(self, mock_executor, build_request) -> None:
        """ClickHouse использует DESCRIBE TABLE — другой формат ответа."""
        req = build_request(
            target_key_cols=["report_date", "document_number"],
            target_column="comment_text",
            target_language_column="lang_code",
            target_source_column="source_text",
            target_source_language_column="src_lang",
        )
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "clickhouse"
        instance.get_database_name.return_value = "test_clickhouse"
        instance.execute_and_poll.return_value = {
            "status": "success",
            "raw_response": {
                "columns": [{"name": "name"}, {"name": "type"}],
                "data": [
                    {"name": "report_date", "type": "Date"},
                    {"name": "document_number", "type": "String"},
                    {"name": "lang_code", "type": "String"},
                    {"name": "source_text", "type": "String"},
                    {"name": "src_lang", "type": "String"},
                    {"name": "comment_text", "type": "String"},
                    {"name": "context", "type": "String"},
                    {"name": "is_original", "type": "UInt8"},
                ],
            },
        }

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is True
        assert response.error is None
        assert response.all_present is True
        assert len(response.missing_columns) == 0
        assert len(response.actual_columns) == 8
        assert response.database_name == "test_clickhouse"
        assert response.database_backend == "clickhouse"

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_superset_failed_status(self, mock_executor, build_request) -> None:
        """Superset возвращает failed статус."""
        req = build_request()
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "postgresql"
        instance.get_database_name.return_value = "test_db"
        instance.execute_and_poll.return_value = {
            "status": "failed",
            "error_message": "Query timeout",
        }

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is False
        assert response.error is not None
        assert "Query timeout" in response.error
        assert response.all_present is False

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_executor_exception(self, mock_executor, build_request) -> None:
        """Executor бросает исключение."""
        req = build_request()
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "postgresql"
        instance.get_database_name.return_value = "test_db"
        instance.execute_and_poll.side_effect = ConnectionError("Superset API unavailable")

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is False
        assert response.error is not None
        assert "Superset API unavailable" in response.error
        assert response.all_present is False

    def test_sql_injection_table_name(self, build_request) -> None:
        """Невалидное имя таблицы с пробелом — ошибка."""
        req = build_request(target_table="my table; DROP TABLE students; --")
        config_manager = MagicMock()

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is False
        assert response.error is not None
        assert "Invalid target table name" in response.error
        assert response.all_present is False

    def test_sql_injection_schema_name(self, build_request) -> None:
        """Невалидное имя схемы с DROP — ошибка."""
        req = build_request(target_schema="public; DROP TABLE students; --")
        config_manager = MagicMock()

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is False
        assert response.error is not None
        assert "Invalid target schema name" in response.error
        assert response.all_present is False

    @patch("src.plugins.translate.service_target_schema.SupersetSqlLabExecutor")
    def test_table_not_exists(self, mock_executor, build_request) -> None:
        """Таблица не существует — пустой data."""
        req = build_request()
        config_manager = MagicMock()

        instance = mock_executor.return_value
        instance.resolve_database_id.return_value = None
        instance.get_database_backend.return_value = "postgresql"
        instance.get_database_name.return_value = "test_db"
        instance.execute_and_poll.return_value = {
            "status": "success",
            "raw_response": {
                "columns": [{"name": "column_name"}],
                "data": [],
            },
        }

        response = validate_target_table_schema(req, config_manager)
        assert response.table_exists is False
        assert response.error is None
        assert response.all_present is False
        assert len(response.missing_columns) == 4  # id, translated_text, context, is_original
# endregion TestValidateTargetTableSchema
# endregion TargetSchemaValidationTests
