Feature: Database resource types and transaction sandbox
  As a CleverAgents developer
  I want database resource types with transaction-based sandboxing
  So that I can manage database resources with isolation and rollback

  # ---- Resource Type Definitions ----

  Scenario: Postgres resource type is defined
    Given the database type definitions are loaded
    Then the "postgres" type definition should exist
    And the "postgres" type should have sandbox strategy "transaction_rollback"
    And the "postgres" type should have a "host" cli argument
    And the "postgres" type should have a "port" cli argument
    And the "postgres" type should have a "dbname" cli argument
    And the "postgres" type should have a "user" cli argument
    And the "postgres" type should have a "password" cli argument
    And the "postgres" type should have a "connection-string" cli argument

  Scenario: MySQL resource type is defined
    Given the database type definitions are loaded
    Then the "mysql" type definition should exist
    And the "mysql" type should have sandbox strategy "transaction_rollback"
    And the "mysql" type should have a "host" cli argument
    And the "mysql" type should have a "port" cli argument

  Scenario: SQLite resource type is defined
    Given the database type definitions are loaded
    Then the "sqlite" type definition should exist
    And the "sqlite" type should have sandbox strategy "transaction_rollback"
    And the "sqlite" type should have a "path" cli argument

  Scenario: DuckDB resource type is defined
    Given the database type definitions are loaded
    Then the "duckdb" type definition should exist
    And the "duckdb" type should have sandbox strategy "transaction_rollback"
    And the "duckdb" type should have a "path" cli argument

  # ---- Connection Argument Validation ----

  Scenario: Resolve postgres connection args from properties
    Given postgres connection properties with host "db.example.com" port 5432 dbname "mydb"
    When I resolve connection args for "postgres"
    Then the resolved args should have host "db.example.com"
    And the resolved args should have port 5432
    And the resolved args should have dbname "mydb"

  Scenario: Resolve postgres connection args from connection string
    Given postgres properties with connection string "postgresql://admin:secret@db:5432/app"
    When I resolve connection args for "postgres"
    Then the resolved args should have connection string "postgresql://admin:secret@db:5432/app"

  Scenario: Resolve postgres credentials from env vars
    Given postgres properties without explicit credentials
    And the env var "CLEVERAGENTS_DB_USER" is set to "envuser"
    And the env var "CLEVERAGENTS_DB_PASSWORD" is set to "envpass"
    When I resolve connection args for "postgres"
    Then the resolved args should have user "envuser"
    And the resolved args should have password "envpass"

  Scenario: Resolve sqlite connection args
    Given sqlite properties with path "/tmp/test.db"
    When I resolve connection args for "sqlite"
    Then the resolved args should have path "/tmp/test.db"

  Scenario: Resolve duckdb connection args with default memory
    Given duckdb properties with no path
    When I resolve connection args for "duckdb"
    Then the resolved args should have path ":memory:"

  # ---- Credential Masking in Error Messages ----

  Scenario: Credential masking in postgres validation message
    Given postgres connection properties with user "admin" and password "s3cret"
    When I validate the "postgres" connection
    Then the validation message should not contain "s3cret"
    And the validation result should be successful

  Scenario: Connection string credentials are masked
    Given postgres properties with connection string "postgresql://admin:s3cret@db:5432/app"
    When I validate the "postgres" connection
    Then the validation message should not contain "s3cret"

  # ---- Read-Only Enforcement ----

  Scenario: Read-only transaction sandbox rejects writes
    Given a temporary SQLite database with a test table
    And a transaction sandbox in read-only mode
    When I attempt to insert data via the sandbox
    Then the sandbox should reject the write operation

  # ---- Transaction Sandbox Create / Commit / Rollback ----

  Scenario: Transaction sandbox lifecycle create and commit
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-TX-001"
    And I insert a row via the sandbox
    And I commit the sandbox
    Then the row should be persisted in the database

  Scenario: Transaction sandbox lifecycle create and rollback
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-TX-002"
    And I insert a row via the sandbox
    And I rollback the sandbox
    Then the row should not be persisted in the database

  Scenario: Transaction sandbox cleanup closes connection
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-TX-003"
    And I cleanup the sandbox
    Then the sandbox status should be "cleaned_up"

  # ---- SQLite Resource Handler Roundtrip ----

  Scenario: SQLite validate_connection success
    Given a temporary SQLite database
    When I validate the sqlite connection
    Then the sqlite validation should succeed

  Scenario: SQLite validate_connection with memory database
    When I validate an in-memory sqlite connection
    Then the sqlite validation should succeed

  # ---- DatabaseResourceHandler Protocol ----

  Scenario: DatabaseResourceHandler satisfies ResourceHandler protocol
    Given a DatabaseResourceHandler instance
    Then the handler should satisfy the ResourceHandler protocol

  Scenario: DatabaseResourceHandler uses transaction_rollback strategy
    Given a DatabaseResourceHandler instance
    Then the handler default strategy should be "transaction_rollback"

  # ---- SandboxFactory transaction_rollback routing ----

  Scenario: SandboxFactory creates TransactionSandbox for transaction_rollback
    Given the sandbox factory is available
    When a sandbox is requested for resource "db-1" at ":memory:" using transaction_rollback
    Then the created sandbox should be a TransactionSandbox

  Scenario: SandboxFactory reports transaction_rollback as supported
    Given the sandbox factory is available
    Then the factory should report "transaction_rollback" as supported

  # ---- TransactionSandbox edge cases ----

  Scenario: TransactionSandbox rejects empty resource_id
    When I create a TransactionSandbox with empty resource_id
    Then the edge error should be a ValueError mentioning "resource_id"

  Scenario: TransactionSandbox rejects empty original_path
    When I create a TransactionSandbox with empty original_path
    Then the edge error should be a ValueError mentioning "original_path"

  Scenario: TransactionSandbox rejects empty plan_id
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I try to create the sandbox with an empty plan_id
    Then the edge error should be a ValueError mentioning "plan_id"

  Scenario: TransactionSandbox get_path returns db path
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-PATH"
    Then get_path should return the database path

  Scenario: TransactionSandbox get_path rejects traversal
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-TRAV"
    Then get_path with traversal should raise ValueError

  Scenario: TransactionSandbox get_path in wrong state raises
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    Then get_path before create should raise SandboxStateError

  Scenario: TransactionSandbox commit in wrong state raises
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    Then commit before create should raise SandboxStateError

  Scenario: TransactionSandbox rollback in wrong state raises
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    Then rollback before create should raise SandboxStateError

  Scenario: TransactionSandbox cleanup is idempotent
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-IDEM"
    And I cleanup the sandbox
    And I cleanup the sandbox again
    Then the sandbox status should be "cleaned_up"

  Scenario: TransactionSandbox execute in wrong state raises
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    Then execute before create should raise SandboxStateError

  Scenario: TransactionSandbox cleanup with active transaction
    Given a temporary SQLite database with a test table
    And a transaction sandbox for the database
    When I create the sandbox with plan "PLAN-ACT"
    And I insert a row via the sandbox
    And I cleanup the sandbox
    Then the sandbox status should be "cleaned_up"

  Scenario: DuckDB validate_connection stub
    When I validate a duckdb connection with path ":memory:"
    Then the duckdb validation should succeed with a stub message

  Scenario: MySQL validate_connection stub
    Given mysql connection properties with host "db.example.com" and port 3306
    When I validate the "mysql" connection
    Then the validation result should be successful

  Scenario: Unknown database type validation
    When I validate an unknown database type
    Then the unknown validation should fail

  Scenario: Resolve mysql connection args from connection string
    Given mysql properties with connection string "mysql://user:pass@host:3306/db"
    When I resolve connection args for "mysql"
    Then the resolved args should have connection string "mysql://user:pass@host:3306/db"
