"""HIGH PRIORITY: Tests for SQL validation — injection prevention."""

import pytest
import sys
from pathlib import Path

sys.path.insert(0, str(Path(__file__).resolve().parent.parent / "backend"))

from security.sql_validator import validate_sql, SQLValidationError, extract_tables_from_sql


class TestValidSQLPasses:
    """Test that legitimate SELECT queries pass validation."""

    def test_simple_select(self):
        sql = 'SELECT * FROM v3q_data."orders"'
        result = validate_sql(sql)
        assert "SELECT" in result

    def test_select_with_where(self):
        sql = 'SELECT order_id, total FROM v3q_data."orders" WHERE total > 100'
        result = validate_sql(sql)
        assert result

    def test_select_with_join(self):
        sql = '''
            SELECT o.order_id, c.name
            FROM v3q_data."orders" o
            JOIN v3q_data."customers" c ON o.customer_id = c.id
        '''
        result = validate_sql(sql)
        assert result

    def test_select_with_aggregation(self):
        sql = '''
            SELECT customer_id, COUNT(*) as order_count, SUM(total) as total_revenue
            FROM v3q_data."orders"
            GROUP BY customer_id
            HAVING COUNT(*) > 5
            ORDER BY total_revenue DESC
        '''
        result = validate_sql(sql)
        assert result

    def test_select_with_subquery(self):
        sql = '''
            SELECT * FROM v3q_data."orders"
            WHERE customer_id IN (SELECT id FROM v3q_data."customers" WHERE country = 'US')
        '''
        result = validate_sql(sql)
        assert result

    def test_select_with_cte(self):
        sql = '''
            WITH top_customers AS (
                SELECT customer_id, SUM(total) as revenue
                FROM v3q_data."orders"
                GROUP BY customer_id
            )
            SELECT * FROM top_customers ORDER BY revenue DESC LIMIT 10
        '''
        result = validate_sql(sql)
        assert result

    def test_select_with_limit(self):
        sql = 'SELECT * FROM v3q_data."products" LIMIT 100'
        result = validate_sql(sql)
        assert result

    def test_select_with_case(self):
        sql = '''
            SELECT order_id,
                   CASE WHEN total > 1000 THEN 'large'
                        WHEN total > 100 THEN 'medium'
                        ELSE 'small' END as order_size
            FROM v3q_data."orders"
        '''
        result = validate_sql(sql)
        assert result

    def test_trailing_semicolon_stripped(self):
        sql = 'SELECT * FROM v3q_data."orders";'
        result = validate_sql(sql)
        assert not result.endswith(";")


class TestDangerousSQL:
    """Test that dangerous SQL is blocked."""

    def test_blocks_insert(self):
        with pytest.raises(SQLValidationError, match="INSERT"):
            validate_sql('INSERT INTO v3q_data."orders" (id) VALUES (1)')

    def test_blocks_update(self):
        with pytest.raises(SQLValidationError, match="UPDATE"):
            validate_sql('UPDATE v3q_data."orders" SET total = 0')

    def test_blocks_delete(self):
        with pytest.raises(SQLValidationError, match="DELETE"):
            validate_sql('DELETE FROM v3q_data."orders"')

    def test_blocks_drop(self):
        with pytest.raises(SQLValidationError, match="DROP"):
            validate_sql('DROP TABLE v3q_data."orders"')

    def test_blocks_alter(self):
        with pytest.raises(SQLValidationError, match="ALTER"):
            validate_sql('ALTER TABLE v3q_data."orders" ADD COLUMN hack TEXT')

    def test_blocks_truncate(self):
        with pytest.raises(SQLValidationError, match="TRUNCATE"):
            validate_sql('TRUNCATE TABLE v3q_data."orders"')

    def test_blocks_create(self):
        with pytest.raises(SQLValidationError, match="CREATE"):
            validate_sql('CREATE TABLE v3q_data."hack" (id INT)')

    def test_blocks_exec(self):
        with pytest.raises(SQLValidationError):
            validate_sql("EXEC xp_cmdshell 'dir'")

    def test_blocks_copy(self):
        with pytest.raises(SQLValidationError):
            validate_sql("COPY v3q_data.orders TO '/tmp/data.csv'")

    def test_blocks_pg_read_file(self):
        with pytest.raises(SQLValidationError, match="pg_read_file"):
            validate_sql("SELECT pg_read_file('/etc/passwd')")

    def test_blocks_xp_cmdshell(self):
        with pytest.raises(SQLValidationError):
            validate_sql("EXEC xp_cmdshell 'whoami'")


class TestInjectionPrevention:
    """Test SQL injection attack patterns."""

    def test_blocks_statement_batching(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT 1; DROP TABLE orders")

    def test_blocks_comment_injection(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT * FROM orders -- WHERE restricted = true")

    def test_blocks_block_comments(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT * FROM orders /* WHERE restricted = true */")

    def test_blocks_union_injection(self):
        with pytest.raises(SQLValidationError, match="UNION"):
            validate_sql("SELECT name FROM users UNION ALL SELECT password FROM credentials")

    def test_blocks_waitfor_delay(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT 1; WAITFOR DELAY '00:00:10'")

    def test_blocks_pg_sleep(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT pg_sleep(10)")

    def test_blocks_multiple_statements(self):
        with pytest.raises(SQLValidationError):
            validate_sql("SELECT 1; SELECT 2")

    def test_empty_query_rejected(self):
        with pytest.raises(SQLValidationError, match="Empty"):
            validate_sql("")

    def test_whitespace_only_rejected(self):
        with pytest.raises(SQLValidationError, match="Empty"):
            validate_sql("   ")


class TestSchemaAccessControl:
    """Test that queries can only access v3q_data and v3q_results."""

    def test_blocks_v3q_config_access(self):
        with pytest.raises(SQLValidationError, match="v3q_config"):
            validate_sql("SELECT * FROM v3q_config.database_connections")

    def test_blocks_pg_catalog(self):
        with pytest.raises(SQLValidationError, match="pg_catalog"):
            validate_sql("SELECT * FROM pg_catalog.pg_tables")

    def test_blocks_information_schema(self):
        with pytest.raises(SQLValidationError, match="information_schema"):
            validate_sql("SELECT * FROM information_schema.tables")

    def test_allows_v3q_data_access(self):
        result = validate_sql('SELECT * FROM v3q_data."orders"')
        assert result

    def test_allows_v3q_results_access(self):
        result = validate_sql('SELECT * FROM v3q_results."result_123"')
        assert result


class TestTableExtraction:
    """Test table name extraction from SQL."""

    def test_simple_from(self):
        tables = extract_tables_from_sql('SELECT * FROM v3q_data."orders"')
        assert "v3q_data.orders" in tables

    def test_join_tables(self):
        tables = extract_tables_from_sql('''
            SELECT * FROM v3q_data."orders" o
            JOIN v3q_data."customers" c ON o.cid = c.id
        ''')
        assert "v3q_data.orders" in tables
        assert "v3q_data.customers" in tables
