Feature: Database migration lifecycle
  As a CleverAgents administrator
  I want to manage database schema via Alembic migrations
  So that I can safely evolve the schema with rollback capability

  Background:
    Given a fresh in-memory database for migration lifecycle testing

  Scenario: Apply all migrations forward on a fresh database
    When I run all migrations forward to head
    Then the database should have all expected tables
    And the current revision should be the head revision

  Scenario: Roll back all migrations to base
    Given all migrations have been applied
    When I downgrade all migrations to base
    Then the current revision should be None
    And the database should have no application tables

  Scenario: Round-trip forward then rollback to initial
    Given all migrations have been applied
    When I downgrade to the initial migration "001_initial_schema"
    Then the current revision should be "001_initial_schema"
    When I upgrade back to head
    Then the current revision should be the head revision

  Scenario: CLI db upgrade command applies migrations
    When I invoke the db upgrade CLI command
    Then the CLI should report a successful upgrade
    And the database should have all expected tables

  Scenario: CLI db current command shows revision
    Given all migrations have been applied
    When I invoke the db current CLI command
    Then the CLI should display the current revision

  Scenario: CLI db downgrade command rolls back
    Given all migrations have been applied
    When I invoke the db downgrade CLI command with revision "m6_004_container_metadata_column"
    Then the CLI should report a successful downgrade

  Scenario: Stamp logic detects pre-Alembic database
    Given a database with legacy tables but no alembic_version
    When I run init_or_upgrade on the legacy database
    Then the database should be stamped with alembic_version
    And the database revision should be at head

  Scenario: init_database creates a valid schema
    When I call init_database with an in-memory URL
    Then the resulting engine should have a valid schema