Feature: DatabaseResourceHandler CRUD and checkpoint methods
  As a CleverAgents developer
  I want DatabaseResourceHandler to implement full CRUD and checkpoint methods
  So that SQLite databases can be read, written, deleted, listed, diffed, and checkpointed

  Issue #1241: DatabaseResourceHandler CRUD and checkpoint methods

  # ============================================================
  # read() — SQLite schema query
  # ============================================================

  Scenario: read() returns schema for SQLite database with tables
    Given a SQLite database with tables "users" and "orders"
    When I read from the database handler
    Then the db read content should contain "users"
    And the db read content should contain "orders"
    And the db read content encoding should be "utf-8"
    And the db read content hash should not be empty

  Scenario: read() returns empty schema for empty SQLite database
    Given an empty SQLite database file
    When I read from the database handler
    Then the db read content encoding should be "utf-8"
    And the db read content data should be empty or whitespace

  Scenario: read() returns connection-info summary for remote database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    When I read from the database handler
    Then the db read content should contain "postgres"
    And the db read content should contain "mydb"
    And the db read content encoding should be "utf-8"

  Scenario: read() returns empty content for resource with no location
    Given a database resource with no location
    When I read from the database handler
    Then the db read content data should be empty or whitespace

  Scenario: read() handles SQLite error gracefully
    Given a database resource pointing to a non-existent SQLite path
    When I read from the database handler
    Then no db exceptions should have been raised

  # ============================================================
  # write() — SQLite SQL execution
  # ============================================================

  Scenario: write() executes SQL on SQLite database
    Given a SQLite database with table "items"
    When I write SQL "INSERT INTO items (id, name) VALUES (1, 'apple')" to the database handler
    Then the db write result should be successful
    And the SQLite table "items" should contain 1 row

  Scenario: write() creates a new table via SQL
    Given an empty SQLite database file
    When I write SQL "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)" to the database handler
    Then the db write result should be successful
    And the SQLite database should have table "products"

  Scenario: write() returns not-supported for remote postgres database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    When I write SQL "SELECT 1" to the database handler
    Then the db write result should not be successful
    And the db write result message should mention "not supported"

  Scenario: write() returns not-supported for remote mysql database
    Given a remote mysql database resource with location "mysql://localhost/mydb"
    When I write SQL "SELECT 1" to the database handler
    Then the db write result should not be successful
    And the db write result message should mention "not supported"

  Scenario: write() returns failure for resource with no location
    Given a database resource with no location
    When I write SQL "SELECT 1" to the database handler
    Then the db write result should not be successful

  Scenario: write() returns failure for invalid SQL
    Given an empty SQLite database file
    When I write SQL "THIS IS NOT SQL" to the database handler
    Then the db write result should not be successful
    And the db write result message should mention "error"

  # ============================================================
  # delete() — SQLite DROP TABLE
  # ============================================================

  Scenario: delete() drops a table from SQLite database
    Given a SQLite database with table "temp_data"
    When I delete table "temp_data" from the database handler
    Then the db delete result should be successful
    And the SQLite database should not have table "temp_data"

  Scenario: delete() with non-existent table succeeds (DROP TABLE IF EXISTS)
    Given an empty SQLite database file
    When I delete table "ghost_table" from the database handler
    Then the db delete result should be successful

  Scenario: delete() with empty path returns failure
    Given an empty SQLite database file
    When I delete with empty path from the database handler
    Then the db delete result should not be successful
    And the db delete result message should mention "table name"

  Scenario: delete() returns not-supported for remote postgres database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    When I delete table "users" from the database handler
    Then the db delete result should not be successful
    And the db delete result message should mention "not supported"

  Scenario: delete() returns failure for resource with no location
    Given a database resource with no location
    When I delete table "users" from the database handler
    Then the db delete result should not be successful

  # ============================================================
  # list_children() — SQLite tables/views
  # ============================================================

  Scenario: list_children() returns table names for SQLite database
    Given a SQLite database with tables "alpha" and "beta"
    When I list children from the database handler
    Then the db children list should contain "alpha"
    And the db children list should contain "beta"
    And the db children list should be sorted

  Scenario: list_children() returns empty list for empty SQLite database
    Given an empty SQLite database file
    When I list children from the database handler
    Then the db children list should be empty

  Scenario: list_children() returns empty list for remote database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    When I list children from the database handler
    Then the db children list should be empty

  Scenario: list_children() returns empty list for resource with no location
    Given a database resource with no location
    When I list children from the database handler
    Then the db children list should be empty

  # ============================================================
  # diff() — schema hash comparison
  # ============================================================

  Scenario: diff() detects no changes between identical SQLite databases
    Given a SQLite database with table "same_table"
    And a second SQLite database with the same schema
    When I diff the database handler against the second database
    Then the db diff result should have no changes

  Scenario: diff() detects changes between different SQLite databases
    Given a SQLite database with table "table_a"
    And a second SQLite database with table "table_b"
    When I diff the database handler against the second database
    Then the db diff result should have changes

  Scenario: diff() compares remote database by identity hash
    Given a remote postgres database resource with location "postgresql://localhost/db1"
    And a second remote database location "postgresql://localhost/db2"
    When I diff the database handler against the second remote location
    Then the db diff result should have changes

  # ============================================================
  # create_checkpoint() — SQLite SAVEPOINT
  # ============================================================

  Scenario: create_checkpoint() creates a SAVEPOINT on SQLite database
    Given a SQLite database with table "checkpoint_test"
    And a mock sandbox manager
    When I create a checkpoint with plan "PLAN-CKPT-001" on the database handler
    Then the db checkpoint result should have a checkpoint_id
    And the db checkpoint result plan_id should be "PLAN-CKPT-001"
    And the db checkpoint result message should mention "SAVEPOINT"

  Scenario: create_checkpoint() returns content-hash checkpoint for remote database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    And a mock sandbox manager
    When I create a checkpoint with plan "PLAN-CKPT-002" on the database handler
    Then the db checkpoint result should have a checkpoint_id
    And the db checkpoint result message should mention "hash"

  # ============================================================
  # rollback_to() — SQLite ROLLBACK TO SAVEPOINT
  # ============================================================

  Scenario: rollback_to() rolls back SQLite changes to SAVEPOINT
    Given a SQLite database with table "rollback_test"
    And a mock sandbox manager
    When I create a checkpoint with plan "PLAN-RB-001" on the database handler
    And I insert a row into "rollback_test" via the database handler
    And I rollback to the last checkpoint on the database handler
    Then the db rollback result should be successful
    And the SQLite table "rollback_test" should contain 0 row

  Scenario: rollback_to() returns failure for unknown checkpoint_id
    Given a SQLite database with table "rollback_test2"
    And a mock sandbox manager
    When I rollback to checkpoint "nonexistent-ckpt" on the database handler
    Then the db rollback result should not be successful
    And the db rollback result message should mention "not found"

  Scenario: rollback_to() returns not-supported for remote database
    Given a remote postgres database resource with location "postgresql://localhost/mydb"
    And a mock sandbox manager
    When I rollback to checkpoint "some-ckpt" on the database handler
    Then the db rollback result should not be successful
    And the db rollback result message should mention "not supported"

  # ============================================================
  # Error handling
  # ============================================================

  Scenario: All methods handle missing location gracefully
    Given a database resource with no location
    And a mock sandbox manager
    When I read from the database handler
    And I write SQL "SELECT 1" to the database handler
    And I delete with empty path from the database handler
    And I list children from the database handler
    Then no db exceptions should have been raised