learningcircuit--local-deep-research/tests/database/test_schema_migrations.py

"""
Tests for Database Schema Migrations

Phase 21: Database & Encryption - Schema Migration Tests
Tests database schema creation, versioning, and migrations.
"""

import pytest


class TestSchemaMigrations:
    """Tests for schema migration functionality"""

    def test_initial_schema_creation(self):
        """Test initial schema is created correctly"""
        from local_deep_research.database.models import Base

        # Verify Base.metadata has tables defined
        assert len(Base.metadata.tables) > 0

    def test_migration_models_importable(self):
        """Test all model modules can be imported"""
        # Import each model module to ensure no syntax errors
        from local_deep_research.database.models import auth
        from local_deep_research.database.models import research
        from local_deep_research.database.models import settings
        from local_deep_research.database.models import metrics
        from local_deep_research.database.models import queue

        assert auth is not None
        assert research is not None
        assert settings is not None
        assert metrics is not None
        assert queue is not None

    def test_base_model_columns(self):
        """Test base model has expected columns"""
        from local_deep_research.database.models.base import Base

        # Base should be a declarative base
        assert hasattr(Base, "metadata")

    def test_auth_model_schema(self):
        """Test auth model schema"""
        from local_deep_research.database.models.auth import User

        # Check expected columns exist
        columns = User.__table__.columns.keys()

        assert "id" in columns
        assert "username" in columns
        # Note: passwords are NOT stored in this model - they decrypt user databases

    def test_research_model_schema(self):
        """Test research model schema"""
        from local_deep_research.database.models.research import ResearchHistory

        columns = ResearchHistory.__table__.columns.keys()

        assert "id" in columns
        assert "query" in columns
        assert "status" in columns

    def test_settings_model_schema(self):
        """Test settings model schema"""
        from local_deep_research.database.models.settings import Setting

        columns = Setting.__table__.columns.keys()

        assert "id" in columns
        assert "key" in columns
        assert "value" in columns

    def test_metrics_model_schema(self):
        """Test metrics model schema"""
        from local_deep_research.database.models.metrics import TokenUsage

        columns = TokenUsage.__table__.columns.keys()

        assert "id" in columns

    def test_sorted_tables_order(self):
        """Test tables are sorted correctly for creation"""
        from local_deep_research.database.models import Base

        tables = Base.metadata.sorted_tables

        # Should have multiple tables
        assert len(tables) > 0

        # Tables should be sorted by dependency order
        table_names = [t.name for t in tables]
        assert len(table_names) == len(set(table_names))  # No duplicates


class TestModelRelationships:
    """Tests for model relationships"""

    def test_research_source_relationship(self):
        """Test research model has expected columns"""
        from local_deep_research.database.models.research import ResearchHistory

        # Check research history model exists and has expected columns
        columns = ResearchHistory.__table__.columns.keys()
        assert "id" in columns
        assert "query" in columns

    def test_queued_research_relationship(self):
        """Test queued research model"""
        from local_deep_research.database.models.queued_research import (
            QueuedResearch,
        )

        columns = QueuedResearch.__table__.columns.keys()

        assert "id" in columns
        assert "query" in columns


class TestDatabaseInitialization:
    """Tests for database initialization"""

    def test_initialize_database_function_exists(self):
        """Test initialize_database function exists"""
        from local_deep_research.database.initialize import initialize_database

        assert callable(initialize_database)

    def test_initialize_module_importable(self):
        """Test initialize module can be imported"""
        from local_deep_research.database import initialize

        assert hasattr(initialize, "initialize_database")


class TestConstraints:
    """Tests for database constraints"""

    def test_unique_username_constraint(self):
        """Test unique username constraint on User model"""
        from local_deep_research.database.models.auth import User

        # Check for unique constraint on username
        username_col = User.__table__.columns["username"]
        assert username_col.unique is True

    def test_setting_key_uniqueness(self):
        """Test setting key uniqueness"""
        from local_deep_research.database.models.settings import Setting

        # Key should be unique within user context
        _key_col = Setting.__table__.columns["key"]  # noqa: F841
        # May have unique constraint or unique together with user_id


class TestColumnTypes:
    """Tests for column type definitions"""

    def test_datetime_columns_have_timezone(self):
        """Test datetime columns use timezone-aware type"""
        from local_deep_research.database.models.research import ResearchHistory

        # Check created_at column exists
        if "created_at" in ResearchHistory.__table__.columns:
            created_col = ResearchHistory.__table__.columns["created_at"]
            # Column should exist (type checking varies by dialect)
            assert created_col is not None

    def test_text_columns_for_long_content(self):
        """Test long content uses Text type"""
        from local_deep_research.database.models.research import ResearchHistory

        # Check report column uses Text
        if "report" in ResearchHistory.__table__.columns:
            report_col = ResearchHistory.__table__.columns["report"]
            assert report_col is not None

    def test_json_columns(self):
        """Test JSON column support"""
        from local_deep_research.database.models.settings import Setting

        # Settings may store JSON values
        value_col = Setting.__table__.columns["value"]
        assert value_col is not None


class TestIndexes:
    """Tests for database indexes"""

    def test_primary_key_indexes(self):
        """Test primary key columns are indexed"""
        from local_deep_research.database.models.auth import User

        # Primary key should be indexed by default
        id_col = User.__table__.columns["id"]
        assert id_col.primary_key is True


class TestTableNames:
    """Tests for table naming conventions"""

    def test_table_names_lowercase(self):
        """Test table names are lowercase"""
        from local_deep_research.database.models import Base

        for table in Base.metadata.tables.values():
            assert table.name == table.name.lower()

    def test_no_reserved_keywords(self):
        """Test no reserved SQL keywords used as table names"""
        reserved = {"user", "order", "group", "select", "table", "index"}

        from local_deep_research.database.models import Base

        for table in Base.metadata.tables.values():
            # 'users' is fine, 'user' is reserved
            if table.name in reserved:
                pytest.fail(
                    f"Reserved keyword used as table name: {table.name}"
                )