*** Settings ***
Documentation    E2E test for Workflow Example 5: Database Schema Migration with Safety Nets.
...
...    Advanced scenario using the **review** automation profile.
...    Registers a custom resource type (postgres-db), creates custom
...    skills with spec-aligned database tools (query_db, execute_migration,
...    backfill_column), exercises phased child plan execution via
...    ``plan tree``, attempts checkpoint-based rollback via
...    ``plan rollback``, and verifies migration changes after apply.
...
...    Zero mocking — real CLI, real LLM API keys.
Resource    common_e2e.resource
Suite Setup    WF05 Suite Setup
Suite Teardown    E2E Suite Teardown
Force Tags       E2E

*** Variables ***
${ACTION_NAME}    local/wf05-db-migration
${SKILL_NAME}    local/wf05-db-tools
${RESOURCE_TYPE_NAME}    local/wf05-postgres-db

*** Keywords ***
WF05 Suite Setup
  [Documentation]    E2E Suite Setup plus dynamic actor selection.
  E2E Suite Setup
  # Generate unique suffix for resource/project names to avoid UNIQUE
  # constraint collisions on repeated E2E runs against the same database.
  ${suffix}=    Evaluate    __import__('uuid').uuid4().hex[:12]
  Set Suite Variable    ${RUN_SUFFIX}    ${suffix}
  # Probe the OpenAI API to confirm the key is usable before selecting actor.
  # Falls back to Anthropic when the key is absent or quota-exhausted.
  ${actor}=    Resolve LLM Actor
  Set Suite Variable    ${WF05_ACTOR}    ${actor}

Create DB App Repo
  [Documentation]    Create temp git repo with a Python app simulating
  ...    a database schema and application code.
  ${repo}=    Create Temp Git Repo wf05-db-app-${RUN_SUFFIX}
  Create Directory    ${repo}${/}src
  Create Directory    ${repo}${/}migrations
  Create Directory    ${repo}${/}tests
  ${schema_content}=    Catenate    SEPARATOR=\n
  ...    """Database schema — users table (missing last_login_at)."""
  ...    ${EMPTY}
  ...    USERS_SCHEMA = {
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}"table": "users",
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}"columns": [
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}{"name": "id", "type": "INTEGER", "primary_key": True},
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}{"name": "email", "type": "VARCHAR(255)"},
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}${SPACE}{"name": "created_at", "type": "TIMESTAMP"},
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}],
  ...    }
  Create File    ${repo}${/}src${/}schema.py    ${schema_content}
  ${app_content}=    Catenate    SEPARATOR=\n
  ...    """Application code — user service."""
  ...    from src.schema import USERS_SCHEMA
  ...    ${EMPTY}
  ...    ${EMPTY}
  ...    def get_user(user_id):
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}"""Fetch a user by ID."""
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}return {"id": user_id, "email": "user@example.com"}
  ...    ${EMPTY}
  ...    ${EMPTY}
  ...    def get_user_activity(user_id):
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}"""Get user activity — needs last_login_at but column is missing."""
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}user = get_user(user_id)
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}# BUG: no last_login_at field available
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}return {"user": user, "last_login": None}
  Create File    ${repo}${/}src${/}app.py    ${app_content}
  ${test_content}=    Catenate    SEPARATOR=\n
  ...    """Tests for user service."""
  ...    from src.app import get_user, get_user_activity
  ...    ${EMPTY}
  ...    ${EMPTY}
  ...    def test_get_user():
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}user = get_user(1)
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}assert user["id"] == 1
  ...    ${EMPTY}
  ...    ${EMPTY}
  ...    def test_get_user_activity():
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}result = get_user_activity(1)
  ...    ${SPACE}${SPACE}${SPACE}${SPACE}assert result["last_login"] is None
  Create File    ${repo}${/}tests${/}test_app.py    ${test_content}
  Create File    ${repo}${/}src${/}__init__.py    \n
  Create File    ${repo}${/}tests${/}__init__.py    \n
  Create File    ${repo}${/}migrations${/}__init__.py    \n
  Create File    ${repo}${/}requirements.txt    pytest>=7.0\n
  ${git_add}=    Run Process    git    add    .    cwd=${repo}    timeout=60s    on_timeout=kill
  Should Be Equal As Integers    ${git_add.rc}    0 git add failed: ${git_add.stderr}
  ${git_commit}=    Run Process    git    commit    -m    Initial DB app with missing last_login_at    cwd=${repo}    timeout=60s    on_timeout=kill
  Should Be Equal As Integers    ${git_commit.rc}    0 git commit failed: ${git_commit.stderr}
  RETURN    ${repo}

WF05 Test Teardown
  [Documentation]    Log diagnostic context on failure for debugging.
  ${plan_id}=    Get Variable Value    ${WF05_PLAN_ID}    ${EMPTY}
  IF    '${plan_id}' != ''
  ${status}    ${result}=    Run Keyword And Ignore Error
  ...    Run CleverAgents Command    plan status ${plan_id} --format json    expected_rc=None    timeout=30s
  IF    '${status}' == 'PASS'
  Log    Teardown plan status: ${result.stdout}    WARN
  END
  ${tree_status}    ${tree_result}=    Run Keyword And Ignore Error
  ...    Run CleverAgents Command    plan tree ${plan_id} --format json    expected_rc=None    timeout=30s
  IF    '${tree_status}' == 'PASS'
  Log    Teardown plan tree: ${tree_result.stdout}    WARN
  END
  END

*** Test Cases ***
WF05 Database Schema Migration With Safety Nets Review Profile
  [Documentation]    Full review-profile workflow: register custom resource type,
  ...    register git-checkout resource, create custom skill with
  ...    spec-aligned DB tools (query_db, execute_migration,
  ...    backfill_column), create migration action with review
  [Tags]    tdd_issue tdd_issue_4189
  ...    automation profile, exercise phased child plan execution,
  ...    attempt checkpoint-based rollback, verify migration changes.
  [Timeout]    30 minutes
  [Teardown]    WF05 Test Teardown
  Skip If No LLM Keys
  # Initialise test variable for teardown access.
  Set Test Variable    ${WF05_PLAN_ID}    ${EMPTY}
