#!/usr/bin/env python3
"""
Oracle Database Test Helpers

This module provides unit test helpers and basic functionality tests
for the Oracle database connection and operations.
"""

import logging
from typing import Any, Dict, List

from server.oracle.pool import OraclePool
from server.oracle.exec import OracleExecutor
from server.oracle.meta import OracleMetadata
from server.oracle.plsql import OraclePLSQL


class OracleTests:
    """
    Provides test helpers for Oracle database operations.
    """

    def __init__(self, pool: OraclePool):
        """
        Initialize the test helper with a connection pool.

        Args:
            pool: OraclePool instance
        """
        self.pool = pool
        self.logger = logging.getLogger(__name__)
        self.executor = OracleExecutor(pool)
        self.metadata = OracleMetadata(pool)
        self.plsql = OraclePLSQL(pool)

    async def run_all_tests(self) -> Dict[str, Any]:
        """
        Run all basic tests and return results.

        Returns:
            dict: Test results summary
        """
        tests = [
            ("connection_test", self.test_connection),
            ("basic_query_test", self.test_basic_query),
            ("query_function_test", self.test_query_function),
            ("metadata_test", self.test_metadata),
            ("list_schemas_test", self.test_list_schemas),
            ("list_objects_test", self.test_list_objects),
            ("describe_object_test", self.test_describe_object),
            ("get_ddl_test", self.test_get_ddl),
            ("explain_plan_test", self.test_explain_plan),
            ("exec_plsql_test", self.test_exec_plsql),
            ("create_or_replace_proc_test", self.test_create_or_replace_proc),
            ("call_proc_test", self.test_call_proc),
            ("run_test_test", self.test_run_test),
            ("semicolon_stripping_test", self.test_semicolon_stripping),
            ("error_handling_test", self.test_error_handling),
            ("pool_status_test", self.test_pool_status),
        ]

        results = {
            "status": "completed",
            "total_tests": len(tests),
            "passed": 0,
            "failed": 0,
            "test_results": [],
        }

        for test_name, test_func in tests:
            try:
                self.logger.info(f"Running {test_name}...")
                test_result = await test_func()
                test_result["name"] = test_name

                if test_result.get("status") == "success":
                    results["passed"] += 1
                else:
                    results["failed"] += 1

                results["test_results"].append(test_result)

            except Exception as e:
                self.logger.error(f"Test {test_name} failed with exception: {e}")
                results["failed"] += 1
                results["test_results"].append(
                    {
                        "name": test_name,
                        "status": "error",
                        "message": f"Test failed with exception: {str(e)}",
                    }
                )

        # Overall status
        results["overall_status"] = (
            "success" if results["failed"] == 0 else "partial_failure"
        )

        return results

    async def test_connection(self) -> Dict[str, Any]:
        """
        Test basic database connectivity.

        Returns:
            dict: Connection test result
        """
        try:
            result = await self.executor.ping()

            if result.get("status") == "success":
                return {
                    "status": "success",
                    "message": "Database connection test passed",
                    "details": result,
                }
            else:
                return {
                    "status": "failed",
                    "message": "Database connection test failed",
                    "details": result,
                }

        except Exception as e:
            return {"status": "error", "message": f"Connection test error: {str(e)}"}

    async def test_basic_query(self) -> Dict[str, Any]:
        """
        Test basic query execution.

        Returns:
            dict: Query test result
        """
        try:
            # Test simple SELECT query
            result = await self.executor.execute_query(
                "SELECT 1 as test_value, SYSDATE as current_time FROM DUAL"
            )

            if result.get("status") == "success":
                rows = result.get("rows", [])
                if len(rows) > 0 and rows[0].get("TEST_VALUE") == 1:
                    return {
                        "status": "success",
                        "message": "Basic query test passed",
                        "details": {
                            "returned_rows": len(rows),
                            "sample_row": rows[0] if rows else None,
                        },
                    }
                else:
                    return {
                        "status": "failed",
                        "message": "Basic query returned unexpected results",
                        "details": result,
                    }
            else:
                return {
                    "status": "failed",
                    "message": "Basic query execution failed",
                    "details": result,
                }

        except Exception as e:
            return {"status": "error", "message": f"Basic query test error: {str(e)}"}

    async def test_query_function(self) -> Dict[str, Any]:
        """
        Test the new query() function with parameter binding and safety limits.

        Returns:
            dict: Query function test result
        """
        try:
            # Test 1: Simple query without parameters
            result1 = await self.executor.query(
                "SELECT 1 as test_value, 'hello' as message FROM DUAL"
            )

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Query function test failed - simple query error",
                    "details": result1,
                }

            # Test 2: Query with parameter binding
            result2 = await self.executor.query(
                "SELECT :value1 as bound_value, :value2 as bound_message FROM DUAL",
                params={"value1": 42, "value2": "parameterized"},
            )

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Query function test failed - parameter binding error",
                    "details": result2,
                }

            # Test 3: Query with max_rows limit
            result3 = await self.executor.query(
                "SELECT LEVEL as num FROM DUAL CONNECT BY LEVEL <= 10", max_rows=3
            )

            if result3.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Query function test failed - max_rows test error",
                    "details": result3,
                }

            # Verify truncation worked
            if result3.get("row_count") != 3 or not result3.get("truncated"):
                return {
                    "status": "failed",
                    "message": "Query function test failed - max_rows truncation not working",
                    "details": result3,
                }

            # Test 4: Query with timeout (should be very short for test)
            result4 = await self.executor.query(
                "SELECT SYSDATE as current_time FROM DUAL", timeout_s=5
            )

            if result4.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Query function test failed - timeout test error",
                    "details": result4,
                }

            # Verify timeout_s is in response
            if result4.get("timeout_s") != 5:
                return {
                    "status": "failed",
                    "message": "Query function test failed - timeout_s not in response",
                    "details": result4,
                }

            # Test 5: Verify JSON formatting (check for expected keys)
            expected_keys = [
                "status",
                "query",
                "columns",
                "rows",
                "row_count",
                "total_rows",
                "truncated",
                "limit",
            ]
            for result in [result1, result2, result3, result4]:
                for key in expected_keys:
                    if key not in result:
                        return {
                            "status": "failed",
                            "message": f"Query function test failed - missing key '{key}' in response",
                            "details": result,
                        }

            return {
                "status": "success",
                "message": "Query function test passed - all features working",
                "details": {
                    "simple_query_rows": len(result1.get("rows", [])),
                    "parameter_binding_works": result2.get("rows", [{}])[0].get(
                        "BOUND_VALUE"
                    )
                    == 42,
                    "max_rows_truncation_works": result3.get("truncated") == True,
                    "timeout_enforcement_works": result4.get("timeout_s") == 5,
                    "json_format_complete": True,
                },
            }

        except Exception as e:
            return {
                "status": "error",
                "message": f"Query function test error: {str(e)}",
            }

    async def test_metadata(self) -> Dict[str, Any]:
        """
        Test metadata operations.

        Returns:
            dict: Metadata test result
        """
        try:
            # Test listing tables (synchronous call)
            tables_result = self.metadata.list_tables()

            if tables_result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Metadata test failed - could not list tables",
                    "details": tables_result,
                }

            # Test session info (async call)
            session_result = await self.executor.get_session_info()

            if session_result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Metadata test failed - could not get session info",
                    "details": session_result,
                }

            return {
                "status": "success",
                "message": "Metadata test passed",
                "details": {
                    "tables_found": tables_result.get("count", 0),
                    "session_info": session_result.get("session", {}),
                    "pool_info": session_result.get("pool", {}),
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"Metadata test error: {str(e)}"}

    async def test_list_schemas(self) -> Dict[str, Any]:
        """
        Test list_schemas operation.

        Returns:
            dict: List schemas test result
        """
        try:
            result = self.metadata.list_schemas()

            if result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "List schemas test failed",
                    "details": result,
                }

            schemas = result.get("schemas", [])
            count = result.get("count", 0)
            limit = result.get("limit", 500)
            truncated = result.get("truncated", False)

            # Verify response structure
            expected_fields = ["schemas", "count", "limit", "truncated"]
            for field in expected_fields:
                if field not in result:
                    return {
                        "status": "failed",
                        "message": f"List schemas test failed - missing expected field '{field}'",
                        "details": result,
                    }

            # Verify row limits are applied
            if len(schemas) > limit:
                return {
                    "status": "failed",
                    "message": f"List schemas test failed - returned {len(schemas)} schemas but limit is {limit}",
                    "details": result,
                }

            # If truncated, verify warning is present
            if truncated and "warning" not in result:
                return {
                    "status": "failed",
                    "message": "List schemas test failed - truncated but no warning message",
                    "details": result,
                }

            return {
                "status": "success",
                "message": "List schemas test passed",
                "details": {
                    "schemas_found": count,
                    "sample_schemas": schemas[:5] if schemas else [],
                    "limit_applied": limit,
                    "truncated": truncated,
                    "row_limits_working": len(schemas) <= limit,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"List schemas test error: {str(e)}"}

    async def test_list_objects(self) -> Dict[str, Any]:
        """
        Test list_objects operation with and without owner filter.

        Returns:
            dict: List objects test result
        """
        try:
            # Test 1: List objects for current user (no owner filter)
            result1 = self.metadata.list_objects()

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "List objects test failed - no owner filter",
                    "details": result1,
                }

            # Test 2: Try to list objects with a specific owner (may fail if no access)
            result2 = self.metadata.list_objects(owner="SYSTEM")

            # This might fail due to permissions, which is expected
            if result2.get("status") == "error":
                # Check if it's a permissions error (expected)
                if "insufficient privileges" in result2.get("message", "").lower():
                    test2_status = "success"
                    test2_message = "Permissions error expected for SYSTEM schema"
                else:
                    test2_status = "failed"
                    test2_message = f"Unexpected error: {result2.get('message')}"
            else:
                test2_status = "success"
                test2_message = "Successfully listed objects for SYSTEM schema"

            # Verify response structure for test 1
            objects = result1.get("objects", [])
            count = result1.get("count", 0)
            limit = result1.get("limit", 500)
            truncated = result1.get("truncated", False)

            expected_fields = ["objects", "count", "owner", "limit", "truncated"]
            for field in expected_fields:
                if field not in result1:
                    return {
                        "status": "failed",
                        "message": f"List objects test failed - missing expected field '{field}'",
                        "details": result1,
                    }

            # Verify row limits are applied
            if len(objects) > limit:
                return {
                    "status": "failed",
                    "message": f"List objects test failed - returned {len(objects)} objects but limit is {limit}",
                    "details": result1,
                }

            # If truncated, verify warning is present
            if truncated and "warning" not in result1:
                return {
                    "status": "failed",
                    "message": "List objects test failed - truncated but no warning message",
                    "details": result1,
                }

            return {
                "status": "success",
                "message": "List objects test passed",
                "details": {
                    "current_user_objects_count": count,
                    "current_user_objects": objects[:3] if objects else [],
                    "limit_applied": limit,
                    "truncated": truncated,
                    "row_limits_working": len(objects) <= limit,
                    "owner_filter_test": {
                        "status": test2_status,
                        "message": test2_message,
                        "result": result2,
                    },
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"List objects test error: {str(e)}"}

    async def test_describe_object(self) -> Dict[str, Any]:
        """
        Test describe_object operation for tables and procedures.

        Returns:
            dict: Describe object test result
        """
        try:
            # Test 1: Try to describe a table (DUAL is always available)
            result1 = self.metadata.describe_object("DUAL", "TABLE")

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Describe object test failed - DUAL table",
                    "details": result1,
                }

            # Verify table description structure
            if "columns" not in result1 or "column_count" not in result1:
                return {
                    "status": "failed",
                    "message": "Describe object test failed - missing table columns",
                    "details": result1,
                }

            # Test 2: Try to describe without specifying object type (auto-detection)
            result2 = self.metadata.describe_object("DUAL")

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Describe object test failed - auto-detection",
                    "details": result2,
                }

            # Test 3: Try to describe non-existent object
            result3 = self.metadata.describe_object("NONEXISTENT_TABLE_12345")

            if result3.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Describe object test failed - should error for non-existent object",
                    "details": result3,
                }

            # Test 4: Try unsupported object type
            result4 = self.metadata.describe_object("DUAL", "UNKNOWN_TYPE")

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Describe object test failed - should error for unsupported type",
                    "details": result4,
                }

            return {
                "status": "success",
                "message": "Describe object test passed",
                "details": {
                    "table_description_works": True,
                    "auto_detection_works": result2.get("object_type") == "TABLE",
                    "column_count": result1.get("column_count", 0),
                    "sample_columns": result1.get("columns", [])[:2],
                    "error_handling_works": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {
                "status": "error",
                "message": f"Describe object test error: {str(e)}",
            }

    async def test_get_ddl(self) -> Dict[str, Any]:
        """
        Test get_ddl operation for various object types.

        Returns:
            dict: Get DDL test result
        """
        try:
            # Test 1: Get DDL for DUAL table
            result1 = self.metadata.get_ddl("TABLE", "DUAL")

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Get DDL test failed - DUAL table",
                    "details": result1,
                }

            # Verify DDL response structure
            if "ddl" not in result1 or "ddl_length" not in result1:
                return {
                    "status": "failed",
                    "message": "Get DDL test failed - missing DDL fields",
                    "details": result1,
                }

            # Test 2: Get DDL for non-existent object
            result2 = self.metadata.get_ddl("TABLE", "NONEXISTENT_TABLE_12345")

            if result2.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Get DDL test failed - should error for non-existent object",
                    "details": result2,
                }

            # Test 3: Get DDL with missing parameters
            result3 = self.metadata.get_ddl("", "DUAL")
            result4 = self.metadata.get_ddl("TABLE", "")

            if result3.get("status") != "error" or result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Get DDL test failed - should error for missing parameters",
                    "details": {"result3": result3, "result4": result4},
                }

            return {
                "status": "success",
                "message": "Get DDL test passed",
                "details": {
                    "ddl_retrieval_works": True,
                    "ddl_length": result1.get("ddl_length", 0),
                    "sample_ddl": (
                        result1.get("ddl", "")[:200] + "..."
                        if len(result1.get("ddl", "")) > 200
                        else result1.get("ddl", "")
                    ),
                    "error_handling_works": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"Get DDL test error: {str(e)}"}

    async def test_explain_plan(self) -> Dict[str, Any]:
        """
        Test explain_plan operation for SQL statements.

        Returns:
            dict: Explain plan test result
        """
        try:
            # Test 1: Explain plan for simple query
            result1 = self.metadata.explain_plan("SELECT * FROM DUAL")

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Explain plan test failed - simple query",
                    "details": result1,
                }

            # Verify explain plan response structure
            if "plan" not in result1 or "plan_lines" not in result1:
                return {
                    "status": "failed",
                    "message": "Explain plan test failed - missing plan fields",
                    "details": result1,
                }

            # Test 2: Explain plan for query with WHERE clause
            result2 = self.metadata.explain_plan("SELECT * FROM DUAL WHERE 1=1")

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Explain plan test failed - WHERE clause query",
                    "details": result2,
                }

            # Test 3: Explain plan for invalid SQL
            result3 = self.metadata.explain_plan("INVALID SQL STATEMENT")

            if result3.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Explain plan test failed - should error for invalid SQL",
                    "details": result3,
                }

            # Test 4: Explain plan with empty SQL
            result4 = self.metadata.explain_plan("")

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "Explain plan test failed - should error for empty SQL",
                    "details": result4,
                }

            return {
                "status": "success",
                "message": "Explain plan test passed",
                "details": {
                    "plan_generation_works": True,
                    "plan_lines_count": result1.get("plan_lines", 0),
                    "sample_plan": result1.get("plan", "").split("\n")[:5],
                    "error_handling_works": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"Explain plan test error: {str(e)}"}

    async def test_error_handling(self) -> Dict[str, Any]:
        """
        Test error handling with invalid queries.

        Returns:
            dict: Error handling test result
        """
        try:
            # Test with invalid SQL
            invalid_result = await self.executor.execute_query(
                "SELECT * FROM INVALID_TABLE_NAME_12345"
            )

            if invalid_result.get("status") == "error":
                return {
                    "status": "success",
                    "message": "Error handling test passed - invalid query properly handled",
                    "details": {
                        "error_message": invalid_result.get("message"),
                        "error_code": invalid_result.get("code"),
                    },
                }
            else:
                return {
                    "status": "failed",
                    "message": "Error handling test failed - invalid query should have returned error",
                    "details": invalid_result,
                }

        except Exception as e:
            return {
                "status": "error",
                "message": f"Error handling test error: {str(e)}",
            }

    async def test_pool_status(self) -> Dict[str, Any]:
        """
        Test connection pool status.

        Returns:
            dict: Pool status test result
        """
        try:
            pool_status = self.pool.get_pool_status()

            if pool_status.get("status") == "active":
                return {
                    "status": "success",
                    "message": "Pool status test passed",
                    "details": pool_status,
                }
            else:
                return {
                    "status": "failed",
                    "message": "Pool status test failed - pool not active",
                    "details": pool_status,
                }

        except Exception as e:
            return {"status": "error", "message": f"Pool status test error: {str(e)}"}

    async def test_exec_plsql(self) -> Dict[str, Any]:
        """
        Test exec_plsql operation with various PL/SQL blocks.

        Returns:
            dict: exec_plsql test result
        """
        try:
            # Test 1: Simple PL/SQL block without parameters
            result1 = await self.plsql.exec_plsql("BEGIN NULL; END;")

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "exec_plsql test failed - simple block",
                    "details": result1,
                }

            # Test 2: PL/SQL block with bind variables
            result2 = await self.plsql.exec_plsql(
                "BEGIN :result := :input_value * 2; END;",
                params={"input_value": 21, "result": None},
            )

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "exec_plsql test failed - with parameters",
                    "details": result2,
                }

            # Test 3: PL/SQL block with timeout
            result3 = await self.plsql.exec_plsql("BEGIN NULL; END;", timeout_s=10)

            if result3.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "exec_plsql test failed - with timeout",
                    "details": result3,
                }

            # Test 4: Empty PL/SQL block (should error)
            result4 = await self.plsql.exec_plsql("")

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "exec_plsql test failed - should error for empty block",
                    "details": result4,
                }

            # Test 5: PL/SQL block that sets a variable
            result5 = await self.plsql.exec_plsql(
                "BEGIN :test_var := 'Hello from PL/SQL'; END;",
                params={"test_var": None},
            )

            # Verify response structure
            expected_keys = ["status", "plsql_block", "message"]
            for result in [result1, result2, result3]:
                for key in expected_keys:
                    if key not in result:
                        return {
                            "status": "failed",
                            "message": f"exec_plsql test failed - missing key '{key}' in response",
                            "details": result,
                        }

            return {
                "status": "success",
                "message": "exec_plsql test passed - all features working",
                "details": {
                    "simple_block_works": True,
                    "parameter_binding_works": result2.get("status") == "success",
                    "timeout_enforcement_works": result3.get("timeout_s") == 10,
                    "error_handling_works": result4.get("status") == "error",
                    "variable_assignment_works": result5.get("status") == "success",
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"exec_plsql test error: {str(e)}"}

    async def test_create_or_replace_proc(self) -> Dict[str, Any]:
        """
        Test create_or_replace_proc operation for stored procedure creation.

        Returns:
            dict: create_or_replace_proc test result
        """
        try:
            # Test 1: Create a simple procedure
            proc_name = "TEST_SIMPLE_PROC"
            proc_text = f"""
                CREATE OR REPLACE PROCEDURE {proc_name} AS
                BEGIN
                    NULL;
                END {proc_name};
            """

            result1 = await self.plsql.create_or_replace_proc(proc_name, proc_text)

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "create_or_replace_proc test failed - simple procedure",
                    "details": result1,
                }

            # Test 2: Create a procedure with parameters
            proc_name2 = "TEST_PARAM_PROC"
            proc_text2 = f"""
                CREATE OR REPLACE PROCEDURE {proc_name2}(p_input IN NUMBER, p_output OUT NUMBER) AS
                BEGIN
                    p_output := p_input * 2;
                END {proc_name2};
            """

            result2 = await self.plsql.create_or_replace_proc(proc_name2, proc_text2)

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "create_or_replace_proc test failed - procedure with parameters",
                    "details": result2,
                }

            # Test 3: Create procedure with timeout
            result3 = await self.plsql.create_or_replace_proc(
                "TEST_TIMEOUT_PROC",
                "CREATE OR REPLACE PROCEDURE TEST_TIMEOUT_PROC AS BEGIN NULL; END TEST_TIMEOUT_PROC;",
                timeout_s=60,
            )

            if result3.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "create_or_replace_proc test failed - with timeout",
                    "details": result3,
                }

            # Test 4: Try to create procedure with empty name (should error)
            result4 = await self.plsql.create_or_replace_proc(
                "", "CREATE OR REPLACE PROCEDURE AS BEGIN NULL; END;"
            )

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "create_or_replace_proc test failed - should error for empty name",
                    "details": result4,
                }

            # Test 5: Try to create procedure with empty text (should error)
            result5 = await self.plsql.create_or_replace_proc("TEST_EMPTY", "")

            if result5.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "create_or_replace_proc test failed - should error for empty text",
                    "details": result5,
                }

            # Verify response structure
            expected_keys = ["status", "procedure_name", "message", "procedure_info"]
            for result in [result1, result2, result3]:
                for key in expected_keys:
                    if key not in result:
                        return {
                            "status": "failed",
                            "message": f"create_or_replace_proc test failed - missing key '{key}' in response",
                            "details": result,
                        }

            return {
                "status": "success",
                "message": "create_or_replace_proc test passed - all features working",
                "details": {
                    "simple_procedure_creation_works": True,
                    "parameterized_procedure_creation_works": True,
                    "timeout_enforcement_works": result3.get("timeout_s") == 60,
                    "error_handling_works": result4.get("status") == "error"
                    and result5.get("status") == "error",
                    "procedure_info_returned": result1.get("procedure_info")
                    is not None,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {
                "status": "error",
                "message": f"create_or_replace_proc test error: {str(e)}",
            }
    async def test_create_or_replace_view(self) -> Dict[str, Any]:
        """
        Test CREATE OR REPLACE VIEW handling via executor.query (DDL statements that do not return result sets).
        Returns:
            dict: Test result
        """
        try:
            view_sql = "CREATE OR REPLACE VIEW m_ai_test AS SELECT 1 AS test_col FROM DUAL"
            result = await self.executor.query(view_sql)

            if result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "create_or_replace_view test failed - DDL returned error",
                    "details": result,
                }

            # Optionally verify no rows/columns are returned for DDL
            if result.get("rows") not in ([], None) or result.get("columns") not in ([], None):
                return {
                    "status": "failed",
                    "message": "create_or_replace_view test failed - unexpected result set from DDL",
                    "details": result,
                }

            return {
                "status": "success",
                "message": "create_or_replace_view test passed - DDL executed successfully",
                "details": result,
            }

        except Exception as e:
            return {"status": "error", "message": f"create_or_replace_view test error: {str(e)}"}

    async def test_call_proc(self) -> Dict[str, Any]:
        """
        test_call_proc operation for calling stored procedures.

        Returns:
            dict: call_proc test result
        """
        try:
            # First, create a test procedure to call
            proc_name = "TEST_CALL_PROC"
            proc_text = f"""
                CREATE OR REPLACE PROCEDURE {proc_name}(p_input IN NUMBER, p_output OUT NUMBER, p_result IN OUT VARCHAR2) AS
                BEGIN
                    p_output := p_input * 3;
                    p_result := 'Processed: ' || p_result;
                END {proc_name};
            """

            # Create the procedure
            create_result = await self.plsql.create_or_replace_proc(
                proc_name, proc_text
            )
            if create_result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "call_proc test failed - could not create test procedure",
                    "details": create_result,
                }

            # Test 1: Call procedure with parameters
            result1 = await self.plsql.call_proc(
                proc_name, params={"p_input": 10, "p_output": None, "p_result": "test"}
            )

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "call_proc test failed - procedure call with parameters",
                    "details": result1,
                }

            # Test 2: Call procedure with timeout
            result2 = await self.plsql.call_proc(proc_name, timeout_s=30)

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "call_proc test failed - procedure call with timeout",
                    "details": result2,
                }

            # Test 3: Call non-existent procedure (should error)
            result3 = await self.plsql.call_proc("NONEXISTENT_PROC_12345")

            if result3.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "call_proc test failed - should error for non-existent procedure",
                    "details": result3,
                }

            # Test 4: Call procedure with empty name (should error)
            result4 = await self.plsql.call_proc("")

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "call_proc test failed - should error for empty name",
                    "details": result4,
                }

            # Verify response structure
            expected_keys = ["status", "procedure_name", "message"]
            for result in [result1, result2]:
                for key in expected_keys:
                    if key not in result:
                        return {
                            "status": "failed",
                            "message": f"call_proc test failed - missing key '{key}' in response",
                            "details": result,
                        }

            return {
                "status": "success",
                "message": "call_proc test passed - all features working",
                "details": {
                    "procedure_call_works": True,
                    "parameter_handling_works": result1.get("output_values")
                    is not None,
                    "timeout_enforcement_works": result2.get("timeout_s") == 30,
                    "error_handling_works": result3.get("status") == "error"
                    and result4.get("status") == "error",
                    "output_values_returned": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"call_proc test error: {str(e)}"}

    async def test_run_test(self) -> Dict[str, Any]:
        """
        Test run_test operation for simple procedure testing.

        Returns:
            dict: run_test test result
        """
        try:
            # Test 1: Run a simple test procedure
            test_name = "simple_test"
            test_procedure = """
                CREATE OR REPLACE PROCEDURE TEST_SIMPLE_TEST AS
                BEGIN
                    NULL;
                END TEST_SIMPLE_TEST;
            """

            result1 = await self.plsql.run_test(test_name, test_procedure)

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "run_test test failed - simple test",
                    "details": result1,
                }

            # Test 2: Run a test with parameters
            test_name2 = "parameterized_test"
            test_procedure2 = """
                CREATE OR REPLACE PROCEDURE TEST_PARAMETERIZED_TEST(p_input IN NUMBER) AS
                BEGIN
                    NULL;
                END TEST_PARAMETERIZED_TEST;
            """

            result2 = await self.plsql.run_test(
                test_name2, test_procedure2, test_params={"p_input": 42}
            )

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "run_test test failed - parameterized test",
                    "details": result2,
                }

            # Test 3: Run test with timeout
            result3 = await self.plsql.run_test(
                "timeout_test",
                "CREATE OR REPLACE PROCEDURE TEST_TIMEOUT_TEST AS BEGIN NULL; END TEST_TIMEOUT_TEST;",
                timeout_s=60,
            )

            if result3.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "run_test test failed - with timeout",
                    "details": result3,
                }

            # Test 4: Run test with empty name (should error)
            result4 = await self.plsql.run_test(
                "", "CREATE OR REPLACE PROCEDURE AS BEGIN NULL; END;"
            )

            if result4.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "run_test test failed - should error for empty name",
                    "details": result4,
                }

            # Test 5: Run test with empty procedure text (should error)
            result5 = await self.plsql.run_test("empty_test", "")

            if result5.get("status") != "error":
                return {
                    "status": "failed",
                    "message": "run_test test failed - should error for empty procedure text",
                    "details": result5,
                }

            # Verify response structure
            expected_keys = ["status", "test_name", "procedure_name", "message"]
            for result in [result1, result2, result3]:
                for key in expected_keys:
                    if key not in result:
                        return {
                            "status": "failed",
                            "message": f"run_test test failed - missing key '{key}' in response",
                            "details": result,
                        }

            return {
                "status": "success",
                "message": "run_test test passed - all features working",
                "details": {
                    "simple_test_works": True,
                    "parameterized_test_works": True,
                    "timeout_enforcement_works": result3.get("timeout_s") == 60,
                    "error_handling_works": result4.get("status") == "error"
                    and result5.get("status") == "error",
                    "procedure_cleanup_works": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"run_test test error: {str(e)}"}

    async def test_specific_query(
        self, query: str, expected_rows: int = None
    ) -> Dict[str, Any]:
        """
        Test a specific query and validate results.

        Args:
            query: SQL query to test
            expected_rows: Expected number of rows (optional)

        Returns:
            dict: Query test result
        """
        try:
            result = await self.executor.execute_query(query)

            if result.get("status") == "success":
                actual_rows = len(result.get("rows", []))

                if expected_rows is not None and actual_rows != expected_rows:
                    return {
                        "status": "failed",
                        "message": f"Query test failed - expected {expected_rows} rows, got {actual_rows}",
                        "details": result,
                    }

                return {
                    "status": "success",
                    "message": f"Query test passed - returned {actual_rows} rows",
                    "details": result,
                }
            else:
                return {
                    "status": "failed",
                    "message": "Query test failed - execution error",
                    "details": result,
                }

        except Exception as e:
            return {"status": "error", "message": f"Query test error: {str(e)}"}

    async def test_table_access(
        self, table_name: str, schema: str = None
    ) -> Dict[str, Any]:
        """
        Test access to a specific table.

        Args:
            table_name: Name of the table to test
            schema: Schema name (optional)

        Returns:
            dict: Table access test result
        """
        try:
            # Test table description
            desc_result = await self.metadata.describe_table(table_name, schema)

            if desc_result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": f"Table access test failed - could not describe table {table_name}",
                    "details": desc_result,
                }

            # Test basic SELECT from table
            query = f"SELECT * FROM {schema + '.' if schema else ''}{table_name} WHERE ROWNUM <= 5"
            select_result = await self.executor.execute_query(query)

            if select_result.get("status") != "success":
                return {
                    "status": "failed",
                    "message": f"Table access test failed - could not select from table {table_name}",
                    "details": select_result,
                }

            return {
                "status": "success",
                "message": f"Table access test passed for {table_name}",
                "details": {
                    "table_info": desc_result.get("table"),
                    "column_count": desc_result.get("column_count"),
                    "sample_rows": len(select_result.get("rows", [])),
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"Table access test error: {str(e)}"}

    async def test_semicolon_stripping(self) -> Dict[str, Any]:
        """
        Test automatic stripping of trailing semicolons from SQL queries (ADR-005).

        Returns:
            dict: Semicolon stripping test result
        """
        try:
            # Test 1: Query with single trailing semicolon
            result1 = await self.executor.query("SELECT 1 as test_value FROM DUAL;")

            if result1.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - single semicolon",
                    "details": result1,
                }

            # Test 2: Query with multiple trailing semicolons
            result2 = await self.executor.query("SELECT 2 as test_value FROM DUAL;;")

            if result2.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - multiple semicolons",
                    "details": result2,
                }

            # Test 3: Query without semicolon (should work unchanged)
            result3 = await self.executor.query("SELECT 3 as test_value FROM DUAL")

            if result3.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - no semicolon",
                    "details": result3,
                }

            # Test 4: Query with semicolon and whitespace
            result4 = await self.executor.query("SELECT 4 as test_value FROM DUAL;   ")

            if result4.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - semicolon with whitespace",
                    "details": result4,
                }

            # Test 5: Explain plan with trailing semicolon
            result5 = await self.executor.explain_plan("SELECT 5 as test_value FROM DUAL;")

            if result5.get("status") != "success":
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - explain plan with semicolon",
                    "details": result5,
                }

            # Verify results contain expected data
            expected_values = [1, 2, 3, 4]
            for i, result in enumerate([result1, result2, result3, result4], 1):
                rows = result.get("rows", [])
                if not rows or rows[0].get("TEST_VALUE") != expected_values[i-1]:
                    return {
                        "status": "failed",
                        "message": f"Semicolon stripping test failed - incorrect result for test {i}",
                        "details": result,
                    }

            # Verify explain plan result structure
            if "plan" not in result5 or not result5.get("plan"):
                return {
                    "status": "failed",
                    "message": "Semicolon stripping test failed - explain plan missing plan data",
                    "details": result5,
                }

            return {
                "status": "success",
                "message": "Semicolon stripping test passed - all query types work with/without semicolons",
                "details": {
                    "single_semicolon_works": result1.get("status") == "success",
                    "multiple_semicolons_works": result2.get("status") == "success",
                    "no_semicolon_works": result3.get("status") == "success",
                    "semicolon_with_whitespace_works": result4.get("status") == "success",
                    "explain_plan_with_semicolon_works": result5.get("status") == "success",
                    "results_contain_expected_data": True,
                    "response_structure_valid": True,
                },
            }

        except Exception as e:
            return {"status": "error", "message": f"Semicolon stripping test error: {str(e)}"}
