# region SQLGeneratorTests [TYPE Module]
# @SEMANTICS: test, translate, sql_generator
# @PURPOSE: Tests for SQLGenerator: PostgreSQL INSERT + UPSERT, ClickHouse INSERT, dialect quoting, NULL handling, injection safety.
# @LAYER Test
# @RELATION  BINDS_TO -> [SQLGenerator]
# @TEST_CONTRACT  SQLGenerator.generate -> SQL string, row_count
# @TEST_FIXTURE  sample_rows -> [{"col1": "val1", "col2": 42}, {"col1": "val2", "col2": 99}]
# @TEST_EDGE  empty_rows -> raises ValueError
# @TEST_EDGE  null_values -> properly encoded as NULL
# @TEST_EDGE  sql_injection -> values with single quotes are escaped

import pytest
from typing import Any

from src.plugins.translate.sql_generator import (
    SQLGenerator,
    _encode_sql_value,
    _quote_identifier,
    generate_insert_sql,
    generate_upsert_sql,
)


# region _normalize_sql [TYPE Function]
# @PURPOSE: Normalize SQL for comparison by collapsing whitespace.
def _normalize_sql(sql: str) -> str:
    return " ".join(sql.split())


# endregion _normalize_sql


# region sample_rows [TYPE Function]
# @PURPOSE: Sample rows fixture for SQL generation tests.
@pytest.fixture
def sample_rows() -> list[dict[str, Any]]:
    return [
        {"col1": "hello", "col2": 42, "col3": None},
        {"col1": "world", "col2": 99, "col3": "note"},
    ]


# endregion sample_rows


# region TestQuoteIdentifier [TYPE Class]
# @PURPOSE: Tests for identifier quoting per dialect.
class TestQuoteIdentifier:
    # region test_quote_postgresql [TYPE Function]
    # @PURPOSE: PostgreSQL uses double quotes.
    def test_quote_postgresql(self) -> None:
        assert _quote_identifier("my_column", "postgresql") == '"my_column"'

    # region test_quote_clickhouse [TYPE Function]
    # @PURPOSE: ClickHouse uses backticks.
    def test_quote_clickhouse(self) -> None:
        assert _quote_identifier("my_column", "clickhouse") == "`my_column`"

    # region test_quote_with_existing_quotes [TYPE Function]
    # @PURPOSE: Strips existing quotes before quoting.
    def test_quote_with_existing_quotes(self) -> None:
        assert _quote_identifier('"my_col"', "postgresql") == '"my_col"'
        assert _quote_identifier("`my_col`", "clickhouse") == "`my_col`"

    # region test_quote_schema_table [TYPE Function]
    # @PURPOSE: Schema-qualified table references work.
    def test_quote_schema_table(self) -> None:
        assert _quote_identifier("my_schema", "postgresql") == '"my_schema"'
        assert _quote_identifier("my_table", "postgresql") == '"my_table"'

    # endregion test_quote_postgresql
    # endregion test_quote_clickhouse
    # endregion test_quote_with_existing_quotes
    # endregion test_quote_schema_table
# endregion TestQuoteIdentifier


# region TestEncodeSqlValue [TYPE Class]
# @PURPOSE: Tests for SQL value encoding.
class TestEncodeSqlValue:
    # region test_null [TYPE Function]
    # @PURPOSE: None encodes as NULL.
    def test_null(self) -> None:
        assert _encode_sql_value(None) == "NULL"

    # region test_string [TYPE Function]
    # @PURPOSE: Strings are single-quoted.
    def test_string(self) -> None:
        assert _encode_sql_value("hello") == "'hello'"

    # region test_integer [TYPE Function]
    # @PURPOSE: Integers are not quoted.
    def test_integer(self) -> None:
        assert _encode_sql_value(42) == "42"

    # region test_float [TYPE Function]
    # @PURPOSE: Floats are not quoted.
    def test_float(self) -> None:
        assert _encode_sql_value(3.14) == "3.14"

    # region test_boolean [TYPE Function]
    # @PURPOSE: Booleans encode as TRUE/FALSE.
    def test_boolean(self) -> None:
        assert _encode_sql_value(True) == "TRUE"
        assert _encode_sql_value(False) == "FALSE"

    # region test_sql_injection [TYPE Function]
    # @PURPOSE: Single quotes in strings are escaped.
    def test_sql_injection(self) -> None:
        assert _encode_sql_value("it's a test") == "'it''s a test'"

    # region test_special_chars [TYPE Function]
    # @PURPOSE: Special characters in strings.
    def test_special_chars(self) -> None:
        assert _encode_sql_value("line1\nline2") == "'line1\nline2'"

    # endregion test_null
    # endregion test_string
    # endregion test_integer
    # endregion test_float
    # endregion test_boolean
    # endregion test_sql_injection
    # endregion test_special_chars
# endregion TestEncodeSqlValue


# region TestGenerateInsertSql [TYPE Class]
# @PURPOSE: Tests for plain INSERT SQL generation.
class TestGenerateInsertSql:
    # region test_basic_insert [TYPE Function]
    # @PURPOSE: Generates basic INSERT with VALUES.
    def test_basic_insert(self, sample_rows: list[dict[str, Any]]) -> None:
        sql = generate_insert_sql(
            target_schema=None,
            target_table="my_table",
            columns=["col1", "col2", "col3"],
            rows=sample_rows,
        )
        assert sql.startswith("INSERT INTO my_table")
        assert "VALUES" in sql
        assert "'hello'" in sql
        assert "42" in sql
        assert "NULL" in sql
        assert sql.endswith(";")

    # region test_insert_with_schema [TYPE Function]
    # @PURPOSE: Schema-qualified table name.
    def test_insert_with_schema(self, sample_rows: list[dict[str, Any]]) -> None:
        sql = generate_insert_sql(
            target_schema="public",
            target_table="my_table",
            columns=["col1", "col2", "col3"],
            rows=sample_rows,
        )
        assert "public" in sql
        assert "my_table" in sql

    # region test_empty_columns_raises [TYPE Function]
    # @PURPOSE: Empty columns list raises ValueError.
    def test_empty_columns_raises(self, sample_rows: list[dict[str, Any]]) -> None:
        with pytest.raises(ValueError, match="At least one column"):
            generate_insert_sql(
                target_schema=None,
                target_table="my_table",
                columns=[],
                rows=sample_rows,
            )

    # region test_empty_rows_raises [TYPE Function]
    # @PURPOSE: Empty rows list raises ValueError.
    def test_empty_rows_raises(self) -> None:
        with pytest.raises(ValueError, match="At least one row"):
            generate_insert_sql(
                target_schema=None,
                target_table="my_table",
                columns=["col1"],
                rows=[],
            )

    # region test_null_handling [TYPE Function]
    # @PURPOSE: NULL values are correctly represented.
    def test_null_handling(self) -> None:
        rows = [{"col1": None, "col2": "text"}]
        sql = generate_insert_sql(
            target_schema=None,
            target_table="t",
            columns=["col1", "col2"],
            rows=rows,
        )
        assert "NULL" in sql
        assert "'text'" in sql

    # region test_injection_safety [TYPE Function]
    # @PURPOSE: SQL injection via string values is prevented.
    def test_injection_safety(self) -> None:
        rows = [{"col1": "'; DROP TABLE users; --"}]
        sql = generate_insert_sql(
            target_schema=None,
            target_table="t",
            columns=["col1"],
            rows=rows,
        )
        # The single quote should be doubled, preventing injection
        assert "'';" in sql or "''" in sql
        assert "DROP TABLE" in sql  # It's literal data, not a command

    # endregion test_basic_insert
    # endregion test_insert_with_schema
    # endregion test_empty_columns_raises
    # endregion test_empty_rows_raises
    # endregion test_null_handling
    # endregion test_injection_safety
# endregion TestGenerateInsertSql


# region TestGenerateUpsertSql [TYPE Class]
# @PURPOSE: Tests for PostgreSQL UPSERT SQL generation.
class TestGenerateUpsertSql:
    # region test_basic_upsert [TYPE Function]
    # @PURPOSE: Generates INSERT ... ON CONFLICT DO UPDATE.
    def test_basic_upsert(self, sample_rows: list[dict[str, Any]]) -> None:
        sql = generate_upsert_sql(
            target_schema=None,
            target_table="my_table",
            columns=["col1", "col2", "col3"],
            key_columns=["col1"],
            rows=sample_rows,
        )
        assert "INSERT INTO" in sql
        assert "ON CONFLICT" in sql
        assert "DO UPDATE SET" in sql
        assert "col2 = EXCLUDED.col2" in sql

    # region test_upsert_all_keys [TYPE Function]
    # @PURPOSE: When all columns are keys, use DO NOTHING.
    def test_upsert_all_keys(self) -> None:
        rows = [{"id": 1, "name": "test"}]
        sql = generate_upsert_sql(
            target_schema=None,
            target_table="t",
            columns=["id", "name"],
            key_columns=["id", "name"],
            rows=rows,
        )
        assert "DO NOTHING" in sql
        assert "DO UPDATE" not in sql

    # region test_upsert_empty_keys [TYPE Function]
    # @PURPOSE: Empty key_columns raises ValueError.
    def test_upsert_empty_keys(self, sample_rows: list[dict[str, Any]]) -> None:
        with pytest.raises(ValueError, match="key_columns"):
            generate_upsert_sql(
                target_schema=None,
                target_table="t",
                columns=["col1"],
                key_columns=[],
                rows=sample_rows,
            )

    # endregion test_basic_upsert
    # endregion test_upsert_all_keys
    # endregion test_upsert_empty_keys
# endregion TestGenerateUpsertSql


# region TestSQLGenerator [TYPE Class]
# @PURPOSE: Tests for the full SQLGenerator class.
class TestSQLGenerator:
    # region test_postgresql_insert [TYPE Function]
    # @PURPOSE: PostgreSQL dialect generates proper INSERT.
    def test_postgresql_insert(self, sample_rows: list[dict[str, Any]]) -> None:
        sql, count = SQLGenerator.generate(
            dialect="postgresql",
            target_schema="public",
            target_table="my_table",
            columns=["col1", "col2"],
            rows=sample_rows,
            upsert_strategy="INSERT",
        )
        assert count == 2
        assert "INSERT INTO" in sql
        assert '"my_table"' in sql  # Quoted identifier

    # region test_postgresql_upsert [TYPE Function]
    # @PURPOSE: PostgreSQL dialect generates proper UPSERT.
    def test_postgresql_upsert(self, sample_rows: list[dict[str, Any]]) -> None:
        sql, count = SQLGenerator.generate(
            dialect="postgresql",
            target_schema="public",
            target_table="my_table",
            columns=["col1", "col2", "col3"],
            rows=sample_rows,
            key_columns=["col1"],
            upsert_strategy="MERGE",
        )
        assert count == 2
        assert "ON CONFLICT" in sql

    # region test_clickhouse_insert [TYPE Function]
    # @PURPOSE: ClickHouse dialect generates plain INSERT.
    def test_clickhouse_insert(self, sample_rows: list[dict[str, Any]]) -> None:
        sql, count = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="default",
            target_table="my_table",
            columns=["col1", "col2"],
            rows=sample_rows,
            upsert_strategy="INSERT",
        )
        assert count == 2
        assert "INSERT INTO" in sql
        # ClickHouse uses backticks
        assert "`my_table`" in sql or "my_table" in sql

    # region test_clickhouse_upsert_fallback [TYPE Function]
    # @PURPOSE: ClickHouse with MERGE strategy falls back to plain INSERT with warning.
    def test_clickhouse_upsert_fallback(self, sample_rows: list[dict[str, Any]]) -> None:
        sql, count = SQLGenerator.generate(
            dialect="clickhouse",
            target_schema="default",
            target_table="my_table",
            columns=["col1", "col2"],
            rows=sample_rows,
            key_columns=["col1"],
            upsert_strategy="MERGE",
        )
        assert count == 2
        # ClickHouse doesn't support ON CONFLICT; generates plain INSERT
        assert "INSERT INTO" in sql

    # region test_empty_rows_via_generator [TYPE Function]
    # @PURPOSE: Generator raises on empty rows.
    def test_empty_rows_via_generator(self) -> None:
        with pytest.raises(ValueError, match="At least one row"):
            SQLGenerator.generate(
                dialect="postgresql",
                target_schema=None,
                target_table="t",
                columns=["c"],
                rows=[],
            )

    # region test_generate_batch [TYPE Function]
    # @PURPOSE: generate_batch splits large row sets.
    def test_generate_batch(self, sample_rows: list[dict[str, Any]]) -> None:
        # Create many rows to test batching
        many_rows = sample_rows * 5  # 10 rows
        statements = SQLGenerator.generate_batch(
            dialect="postgresql",
            target_schema="public",
            target_table="t",
            columns=["col1", "col2"],
            rows=many_rows,
            max_rows_per_statement=3,
        )
        assert len(statements) > 1  # Should be split into multiple statements
        total_count = sum(count for _, count in statements)
        assert total_count == 10

    # endregion test_postgresql_insert
    # endregion test_postgresql_upsert
    # endregion test_clickhouse_insert
    # endregion test_clickhouse_upsert_fallback
    # endregion test_empty_rows_via_generator
    # endregion test_generate_batch
# endregion TestSQLGenerator
# endregion SQLGeneratorTests
