"""
Test script for Oracle wrapper implementation (Phase 2).

This script tests the core Oracle functionality to ensure it meets
the acceptance criteria for Phase 2.
"""

import sys
import os

# Add current directory to path for imports
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

from core.oracle import (
    set_nls_lang,
    execute_scalar,
    stream_query,
    create_oracle_wrapper,
)
from core.config import get_config
from core.logger import get_rateloader_logger


def test_nls_lang_setup():
    """Test NLS_LANG environment setup."""
    print("Testing NLS_LANG setup...")

    # Get NLS_LANG from config
    config = get_config()
    nls_lang = config.get("nls_lang")

    # Set NLS_LANG
    set_nls_lang(nls_lang)

    # Verify it was set
    actual_nls_lang = os.environ.get("NLS_LANG")
    if actual_nls_lang == nls_lang:
        print(f"✓ NLS_LANG set correctly: {actual_nls_lang}")
        return True
    else:
        print(
            f"✗ NLS_LANG not set correctly. Expected: {nls_lang}, Got: {actual_nls_lang}"
        )
        return False


def test_oracle_connection():
    """Test Oracle connection using connection string from settings.ini."""
    print("\nTesting Oracle connection...")

    try:
        # Create Oracle wrapper
        oracle = create_oracle_wrapper()

        # Test connection
        if oracle.connect():
            print("✓ Oracle connection established successfully")

            # Test is_connected method
            if oracle.is_connected():
                print("✓ Connection health check passed")
            else:
                print("✗ Connection health check failed")
                return False

            # Clean up
            oracle.disconnect()
            return True
        else:
            print("✗ Failed to establish Oracle connection")
            print("  Note: This may be expected if Oracle database is not available")
            return False

    except Exception as e:
        print(f"✗ Exception during connection test: {str(e)}")
        return False


def test_execute_scalar():
    """Test execute_scalar function with simple query."""
    print("\nTesting execute_scalar function...")

    try:
        # Test with simple query
        result = execute_scalar("SELECT 1 FROM DUAL")

        if result == 1:
            print("✓ execute_scalar returned expected result: 1")
            return True
        else:
            print(f"✗ execute_scalar returned unexpected result: {result}")
            return False

    except Exception as e:
        print(f"✗ Exception during execute_scalar test: {str(e)}")
        print("  Note: This may be expected if Oracle database is not available")
        return False


def test_stream_query():
    """Test stream_query function with simple query."""
    print("\nTesting stream_query function...")

    try:
        # Test with simple query that returns multiple rows
        row_count = 0
        batch_count = 0

        for batch in stream_query(
            "SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 5", arraysize=2
        ):
            batch_count += 1
            row_count += len(batch)
            print(f"  Batch {batch_count}: {len(batch)} rows")

        if row_count == 5 and batch_count == 3:  # 5 rows in 3 batches (2,2,1)
            print(f"✓ stream_query processed {row_count} rows in {batch_count} batches")
            return True
        else:
            print(
                f"✗ stream_query processed {row_count} rows in {batch_count} batches (expected 5 rows, 3 batches)"
            )
            return False

    except Exception as e:
        print(f"✗ Exception during stream_query test: {str(e)}")
        print("  Note: This may be expected if Oracle database is not available")
        return False


def test_oracle_wrapper_class():
    """Test OracleWrapper class functionality."""
    print("\nTesting OracleWrapper class...")

    try:
        # Test context manager
        with create_oracle_wrapper() as oracle:
            if not oracle.is_connected():
                print("✗ OracleWrapper context manager failed to connect")
                return False

            # Test scalar query through wrapper
            result = oracle.execute_scalar("SELECT 'TEST' FROM DUAL")
            if result != "TEST":
                print(f"✗ OracleWrapper scalar query failed: {result}")
                return False

            # Test stream query through wrapper
            rows = list(
                oracle.stream_query("SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3")
            )
            if len(rows) != 3:
                print(
                    f"✗ OracleWrapper stream query failed: expected 3 rows, got {len(rows)}"
                )
                return False

        print("✓ OracleWrapper class functionality passed")
        return True

    except Exception as e:
        print(f"✗ Exception during OracleWrapper test: {str(e)}")
        print("  Note: This may be expected if Oracle database is not available")
        return False


def test_error_handling():
    """Test error handling with invalid SQL."""
    print("\nTesting error handling...")

    try:
        # Test with invalid SQL
        result = execute_scalar("SELECT * FROM INVALID_TABLE")

        if result is None:
            print("✓ Error handling worked correctly for invalid SQL")
            return True
        else:
            print(f"✗ Error handling failed: expected None, got {result}")
            return False

    except Exception as e:
        print(f"✗ Unexpected exception during error handling test: {str(e)}")
        return False


def main():
    """Run all tests."""
    print("=== Oracle Wrapper Phase 2 Tests ===\n")

    # Initialize logging
    logger = get_rateloader_logger()
    logger.info("Starting Oracle wrapper tests")

    tests = [
        test_nls_lang_setup,
        test_oracle_connection,
        test_execute_scalar,
        test_stream_query,
        test_oracle_wrapper_class,
        test_error_handling,
    ]

    passed = 0
    total = len(tests)

    for test in tests:
        try:
            if test():
                passed += 1
        except Exception as e:
            print(f"✗ Test {test.__name__} failed with exception: {str(e)}")

    print(f"\n=== Test Results ===")
    print(f"Passed: {passed}/{total}")

    if passed == total:
        print("✓ All tests passed!")
        return True
    else:
        print("✗ Some tests failed.")
        print("  Note: Oracle connection tests may fail if database is not available.")
        print("  This is expected in development environments without Oracle access.")
        return False


if __name__ == "__main__":
    success = main()
    sys.exit(0 if success else 1)
