"""
Tests for database configuration and connectivity

Tests database session management, connection pooling, and async operations
"""

import pytest
from sqlalchemy import select, text
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.user import User


@pytest.mark.asyncio
async def test_database_connection(db_session: AsyncSession):
    """Test basic database connectivity"""
    result = await db_session.execute(text("SELECT 1"))
    assert result.scalar() == 1


@pytest.mark.asyncio
async def test_database_session_commit(db_session: AsyncSession):
    """Test database session commit functionality"""
    user = User(
        azure_ad_id="test-db-session",
        email="dbtest@example.com",
        display_name="DB Test User",
    )

    db_session.add(user)
    await db_session.commit()
    await db_session.refresh(user)

    assert user.id is not None
    assert user.created_at is not None


@pytest.mark.asyncio
async def test_database_session_rollback(db_session: AsyncSession):
    """Test database session rollback functionality"""
    user = User(
        azure_ad_id="test-rollback",
        email="rollback@example.com",
        display_name="Rollback Test",
    )

    db_session.add(user)
    await db_session.flush()

    # Get the user ID before rollback
    user_id = user.id
    assert user_id is not None

    # Rollback the transaction
    await db_session.rollback()

    # Verify user was not persisted
    result = await db_session.execute(
        select(User).where(User.id == user_id)
    )
    assert result.scalar_one_or_none() is None


@pytest.mark.asyncio
async def test_database_query_execution(db_session: AsyncSession):
    """Test query execution with filters"""
    # Create test users
    user1 = User(
        azure_ad_id="query-test-1",
        email="query1@example.com",
        display_name="Query User 1",
    )
    user2 = User(
        azure_ad_id="query-test-2",
        email="query2@example.com",
        display_name="Query User 2",
    )

    db_session.add_all([user1, user2])
    await db_session.commit()

    # Query by email
    result = await db_session.execute(
        select(User).where(User.email == "query1@example.com")
    )
    found_user = result.scalar_one()

    assert found_user.azure_ad_id == "query-test-1"
    assert found_user.display_name == "Query User 1"


@pytest.mark.asyncio
async def test_database_bulk_insert(db_session: AsyncSession):
    """Test bulk insert operations"""
    users = [
        User(
            azure_ad_id=f"bulk-{i}",
            email=f"bulk{i}@example.com",
            display_name=f"Bulk User {i}",
        )
        for i in range(5)
    ]

    db_session.add_all(users)
    await db_session.commit()

    # Verify all users were inserted
    result = await db_session.execute(
        select(User).where(User.azure_ad_id.like("bulk-%"))
    )
    inserted_users = result.scalars().all()

    assert len(inserted_users) == 5


@pytest.mark.asyncio
async def test_database_transaction_isolation(db_session: AsyncSession):
    """Test transaction isolation"""
    user = User(
        azure_ad_id="isolation-test",
        email="isolation@example.com",
        display_name="Isolation Test",
    )

    db_session.add(user)
    await db_session.flush()

    # User should be visible in current session
    result = await db_session.execute(
        select(User).where(User.email == "isolation@example.com")
    )
    assert result.scalar_one_or_none() is not None

    # But can be rolled back
    await db_session.rollback()

    result = await db_session.execute(
        select(User).where(User.email == "isolation@example.com")
    )
    assert result.scalar_one_or_none() is None


@pytest.mark.asyncio
async def test_database_unique_constraint(db_session: AsyncSession):
    """Test unique constraint violations"""
    user1 = User(
        azure_ad_id="unique-test",
        email="unique@example.com",
        display_name="Unique Test 1",
    )

    db_session.add(user1)
    await db_session.commit()

    # Try to insert another user with same email
    user2 = User(
        azure_ad_id="unique-test-2",
        email="unique@example.com",  # Duplicate email
        display_name="Unique Test 2",
    )

    db_session.add(user2)

    with pytest.raises(Exception):  # Should raise integrity error
        await db_session.commit()

    await db_session.rollback()