Feature: Resource registry database tables
  As a developer working on the resource registry infrastructure
  I need database tables for resource types, resources, and resource edges
  So that the resource DAG can be persisted and queried

  Background:
    Given a resource registry in-memory database is initialized

  # ---------------------------------------------------------------------------
  # Table existence
  # ---------------------------------------------------------------------------

  Scenario: Resource registry tables exist after schema creation
    Then the resource registry database should contain table "resource_types"
    And the resource registry database should contain table "resources"
    And the resource registry database should contain table "resource_edges"

  # ---------------------------------------------------------------------------
  # ResourceTypeModel CRUD
  # ---------------------------------------------------------------------------

  Scenario: Persist and retrieve a physical resource type
    When I create a resource type "builtin/git-checkout" with kind "physical" and user_addable true
    Then I can retrieve resource type "builtin/git-checkout"
    And the retrieved resource type namespace is "builtin"
    And the retrieved resource type kind is "physical"
    And the retrieved resource type user_addable is true

  Scenario: Persist and retrieve a virtual resource type
    When I create a resource type "builtin/file" with kind "virtual" and user_addable false
    Then I can retrieve resource type "builtin/file"
    And the retrieved resource type kind is "virtual"
    And the retrieved resource type user_addable is false

  Scenario: Resource type stores JSON fields correctly
    When I create a resource type "builtin/git-checkout" with JSON fields
    Then the resource type args_schema_json round-trips correctly
    And the resource type allowed_parent_types_json round-trips correctly
    And the resource type allowed_child_types_json round-trips correctly
    And the resource type auto_discover_json round-trips correctly
    And the resource type capabilities_json round-trips correctly

  Scenario: Resource type name uniqueness is enforced
    When I create a resource type "builtin/git-checkout" with kind "physical" and user_addable true
    Then creating a duplicate resource type "builtin/git-checkout" raises an integrity error

  Scenario: Resource type kind check constraint rejects invalid values
    Then creating a resource type with kind "invalid_kind" raises an integrity error

  Scenario: List resource types by namespace
    When I create a resource type "builtin/git-checkout" with kind "physical" and user_addable true
    And I create a resource type "builtin/fs-mount" with kind "physical" and user_addable true
    And I create a resource type "custom/database" with kind "physical" and user_addable true
    Then listing resource types by namespace "builtin" returns 2 results
    And listing resource types by namespace "custom" returns 1 result

  Scenario: List resource types by kind
    When I create a resource type "builtin/git-checkout" with kind "physical" and user_addable true
    And I create a resource type "builtin/file" with kind "virtual" and user_addable false
    Then listing resource types by kind "physical" returns 1 result
    And listing resource types by kind "virtual" returns 1 result

  # ---------------------------------------------------------------------------
  # ResourceModel CRUD
  # ---------------------------------------------------------------------------

  Scenario: Persist and retrieve a named resource
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/my-repo" of type "builtin/git-checkout" with location "/home/user/repo"
    Then I can retrieve the resource by id
    And the retrieved resource namespaced_name is "local/my-repo"
    And the retrieved resource namespace is "local"
    And the retrieved resource type_name is "builtin/git-checkout"
    And the retrieved resource kind is "physical"
    And the retrieved resource location is "/home/user/repo"

  Scenario: Persist a resource without a name (auto-discovered child)
    Given a resource type "builtin/fs-file" exists as virtual
    When I create an unnamed resource of type "builtin/fs-file"
    Then I can retrieve the unnamed resource by id
    And the unnamed resource namespaced_name is null

  Scenario: Resource stores JSON properties correctly
    Given a resource type "builtin/git-checkout" exists
    When I create a resource with JSON properties
    Then the resource properties_json round-trips correctly
    And the resource metadata_json round-trips correctly

  Scenario: Resource kind check constraint rejects invalid values
    Given a resource type "builtin/git-checkout" exists
    Then creating a resource with kind "invalid_kind" raises an integrity error

  Scenario: Resource type FK prevents orphaned resources
    Then creating a resource with non-existent type raises an integrity error

  Scenario: Named resource uniqueness is enforced
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/my-repo" of type "builtin/git-checkout" with location "/path/a"
    Then creating a duplicate named resource "local/my-repo" raises an integrity error

  Scenario: Resource RESTRICT prevents type deletion when resources exist
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/my-repo" of type "builtin/git-checkout" with location "/path"
    Then deleting resource type "builtin/git-checkout" raises an integrity error

  Scenario: Query resources by type
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-mount" exists as physical
    When I create a resource "local/repo1" of type "builtin/git-checkout" with location "/repo1"
    And I create a resource "local/mount1" of type "builtin/fs-mount" with location "/mnt"
    Then querying resources by type "builtin/git-checkout" returns 1 result
    And querying resources by type "builtin/fs-mount" returns 1 result

  Scenario: Query resources by namespace
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/repo1" of type "builtin/git-checkout" with location "/repo1"
    And I create a resource "team/repo2" of type "builtin/git-checkout" with location "/repo2"
    Then querying resources by namespace "local" returns 1 result
    And querying resources by namespace "team" returns 1 result

  # ---------------------------------------------------------------------------
  # ResourceEdgeModel (DAG edges)
  # ---------------------------------------------------------------------------

  Scenario: Create and query parent-child edges
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    When I create an edge from parent to child with link_type "contains"
    Then the edge exists in the database
    And the edge link_type is "contains"
    And querying children of the parent returns 1 result
    And querying parents of the child returns 1 result

  Scenario: Edge composite PK prevents duplicate edges
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    When I create an edge from parent to child with link_type "contains"
    Then creating a duplicate edge raises an integrity error

  Scenario: Self-loop check constraint prevents self-referencing edges
    Given a resource type "builtin/git-checkout" exists
    And a parent resource "local/repo" of type "builtin/git-checkout"
    Then creating a self-loop edge raises an integrity error

  Scenario: Edge link_type check constraint rejects invalid values
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    Then creating an edge with invalid link_type raises an integrity error

  Scenario: CASCADE deletes edges when parent resource is deleted
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    And I create an edge from parent to child with link_type "contains"
    When I delete the parent resource
    Then the edge no longer exists in the database

  Scenario: CASCADE deletes edges when child resource is deleted
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    And I create an edge from parent to child with link_type "contains"
    When I delete the child resource
    Then the edge no longer exists in the database

  Scenario: Multiple parents for a single child resource
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo-a" of type "builtin/git-checkout"
    And a second parent resource "local/repo-b" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    When I create an edge from parent to child with link_type "contains"
    And I create an edge from second parent to child with link_type "references"
    Then querying parents of the child returns 2 results

  Scenario: Multiple children for a single parent resource
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a first child resource of type "builtin/fs-directory"
    And a second child resource of type "builtin/fs-directory"
    When I create edges from parent to both children
    Then querying children of the parent returns 2 results

  # ---------------------------------------------------------------------------
  # ORM relationship navigation
  # ---------------------------------------------------------------------------

  Scenario: Navigate from resource to resource type via relationship
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/repo" of type "builtin/git-checkout" with location "/repo"
    Then navigating from resource to resource_type returns "builtin/git-checkout"

  Scenario: Navigate from resource type to resources via relationship
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/repo1" of type "builtin/git-checkout" with location "/r1"
    And I create a resource "local/repo2" of type "builtin/git-checkout" with location "/r2"
    Then navigating from resource_type to resources returns 2 items

  Scenario: Navigate from resource to child edges via relationship
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    And I create an edge from parent to child with link_type "contains"
    Then navigating from parent to child_edges returns 1 edge
    And navigating from child to parent_edges returns 1 edge

  # ---------------------------------------------------------------------------
  # Timestamps and metadata
  # ---------------------------------------------------------------------------

  Scenario: Resource type timestamps are stored correctly
    When I create a resource type "builtin/git-checkout" with kind "physical" and user_addable true
    Then the resource type has valid created_at and updated_at timestamps

  Scenario: Resource timestamps are stored correctly
    Given a resource type "builtin/git-checkout" exists
    When I create a resource "local/repo" of type "builtin/git-checkout" with location "/repo"
    Then the resource has valid created_at and updated_at timestamps

  Scenario: Edge timestamp is stored correctly
    Given a resource type "builtin/git-checkout" exists
    And a resource type "builtin/fs-directory" exists as physical
    And a parent resource "local/repo" of type "builtin/git-checkout"
    And a child resource of type "builtin/fs-directory"
    When I create an edge from parent to child with link_type "contains"
    Then the edge has a valid created_at timestamp

  # ---------------------------------------------------------------------------
  # Migration smoke tests
  # ---------------------------------------------------------------------------

  Scenario: Resource registry tables exist after migration via init_database
    When I run init_database to simulate migration
    Then the migrated database should contain table "resource_types"
    And the migrated database should contain table "resources"
    And the migrated database should contain table "resource_edges"

  Scenario: Migration is idempotent for resource registry tables
    When I run init_database twice on the same database file
    Then the migrated database should contain table "resource_types"
    And the migrated database should contain table "resources"
    And the migrated database should contain table "resource_edges"
