"""
Database Constants for Test Utilities

Centralized definitions for indexes and triggers to avoid duplication
across test modules.
"""

# Index definitions: (index_name, table, column, unique)
INDEX_DEFINITIONS = {
    "ix_sessions_user_id": ("sessions", "user_id", False),
    "ix_sessions_expires_at": ("sessions", "expires_at", False),
    "ix_streams_status": ("streams", "status", False),
    "ix_streams_created_by": ("streams", "created_by", False),
    "ix_comments_stream_id": ("comments", "stream_id", False),
    "ix_comments_user_id": ("comments", "user_id", False),
    "ix_comments_parent_id": ("comments", "parent_id", False),
    "ix_processing_jobs_stream_id": ("processing_jobs", "stream_id", False),
    "ix_processing_jobs_status": ("processing_jobs", "status", False),
    "ix_activity_log_user_id": ("activity_log", "user_id", False),
    "ix_activity_log_created_at": ("activity_log", "created_at", False),
    "ix_watch_history_user_id": ("watch_history", "user_id", False),
    "ix_watch_history_stream_id": ("watch_history", "stream_id", False),
    "ix_watch_history_last_watched_at": ("watch_history", "last_watched_at", False),
    "ix_twitch_oauth_user_id": ("twitch_oauth", "user_id", True),
    "ix_chapters_stream_id": ("chapters", "stream_id", False),
    "ix_chapters_game_display_name": ("chapters", "game_display_name", False),
    "ix_chat_messages_stream_id": ("chat_messages", "stream_id", False),
    "ix_site_settings_key": ("site_settings", "key", True)
}

# Trigger definitions: (trigger_name, table)
TRIGGER_DEFINITIONS = {
    "update_users_updated_at": "users",
    "update_streams_updated_at": "streams",
    "update_comments_updated_at": "comments",
    "update_twitch_oauth_updated_at": "twitch_oauth",
    "update_site_settings_updated_at": "site_settings"
}


def get_index_sql(index_name: str) -> str:
    """Generate CREATE INDEX SQL for a given index name"""
    if index_name not in INDEX_DEFINITIONS:
        return ""
    
    table, column, unique = INDEX_DEFINITIONS[index_name]
    unique_str = "UNIQUE " if unique else ""
    return f'    op.execute("""\n        CREATE {unique_str}INDEX IF NOT EXISTS {index_name} ON {table}({column});\n    """)\n'


def get_index_downgrade_sql(index_name: str) -> str:
    """Generate DROP INDEX SQL for a given index name"""
    if index_name not in INDEX_DEFINITIONS:
        return ""
    return f'    op.execute("""\n        DROP INDEX IF EXISTS {index_name};\n    """)\n'


def get_trigger_sql(trigger_name: str) -> str:
    """Generate CREATE TRIGGER SQL for a given trigger name"""
    if trigger_name not in TRIGGER_DEFINITIONS:
        return ""
    
    table = TRIGGER_DEFINITIONS[trigger_name]
    return f'''    op.execute("""
        DROP TRIGGER IF EXISTS {trigger_name} ON {table};
        CREATE TRIGGER {trigger_name}
        BEFORE UPDATE ON {table}
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
    """)
'''


def get_trigger_downgrade_sql(trigger_name: str) -> str:
    """Generate DROP TRIGGER SQL for a given trigger name"""
    if trigger_name not in TRIGGER_DEFINITIONS:
        return ""
    
    table = TRIGGER_DEFINITIONS[trigger_name]
    return f'    op.execute("""\n        DROP TRIGGER IF EXISTS {trigger_name} ON {table};\n    """)\n'