Feature: Project database tables migration
  As a developer working on project infrastructure
  I need database tables for projects and project resource links
  So that spec-aligned namespaced projects can be persisted and queried

  Background:
    Given a project tables in-memory database is initialized

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

  Scenario: Projects table exists after migration
    Then the project database should contain table "ns_projects"

  Scenario: Project resource links table exists
    Then the project database should contain table "project_resource_links"

  # ---------------------------------------------------------------------------
  # ProjectModel CRUD
  # ---------------------------------------------------------------------------

  Scenario: Can insert and retrieve a project
    When I insert a project "local/my-project" with namespace "local"
    Then I can retrieve project "local/my-project"
    And the retrieved project namespace is "local"
    And the retrieved project description is empty
    And the retrieved project tags_json is "[]"

  Scenario: Can insert a project with description
    When I insert a described project "local/described-proj" with namespace "local" and description "A test project"
    Then I can retrieve project "local/described-proj"
    And the retrieved project description is "A test project"

  Scenario: Can insert a project with JSON fields
    When I insert a project "local/json-proj" with JSON fields
    Then the project invariants_json round-trips correctly
    And the project context_policy_json round-trips correctly
    And the project tags_json round-trips as a list

  Scenario: Project namespaced_name uniqueness is enforced
    When I insert a project "local/unique-test" with namespace "local"
    Then inserting a duplicate project "local/unique-test" raises an integrity error

  # ---------------------------------------------------------------------------
  # ProjectResourceLinkModel CRUD
  # ---------------------------------------------------------------------------

  Scenario: Can link a resource to a project
    Given a resource type "builtin/git-checkout" exists in project db
    And a resource "local/my-repo" of type "builtin/git-checkout" exists in project db
    And a project "local/linked-proj" exists in project db
    When I create a project resource link for project "local/linked-proj" to resource "local/my-repo"
    Then the project resource link can be retrieved
    And the project resource link project_name is "local/linked-proj"
    And the project resource link read_only is false

  Scenario: Can link a resource with alias and read_only
    Given a resource type "builtin/git-checkout" exists in project db
    And a resource "local/aliased-repo" of type "builtin/git-checkout" exists in project db
    And a project "local/aliased-proj" exists in project db
    When I create a project resource link with alias "my-alias" and read_only true
    Then the project resource link alias is "my-alias"
    And the project resource link read_only is true

  Scenario: Duplicate project-resource link is rejected
    Given a resource type "builtin/git-checkout" exists in project db
    And a resource "local/dup-repo" of type "builtin/git-checkout" exists in project db
    And a project "local/dup-proj" exists in project db
    When I create a project resource link for project "local/dup-proj" to resource "local/dup-repo"
    Then creating a duplicate link for project "local/dup-proj" to resource "local/dup-repo" raises an integrity error

  Scenario: Deleting a project cascades to links
    Given a resource type "builtin/git-checkout" exists in project db
    And a resource "local/cascade-repo" of type "builtin/git-checkout" exists in project db
    And a project "local/cascade-proj" exists in project db
    And a project resource link exists for "local/cascade-proj" to "local/cascade-repo"
    When I delete the project "local/cascade-proj"
    Then the project resource link for "local/cascade-proj" should not exist

  Scenario: Deleting a linked resource is restricted
    Given a resource type "builtin/git-checkout" exists in project db
    And a resource "local/restrict-repo" of type "builtin/git-checkout" exists in project db
    And a project "local/restrict-proj" exists in project db
    And a project resource link exists for "local/restrict-proj" to "local/restrict-repo"
    Then deleting the linked resource "local/restrict-repo" raises an integrity error

  # ---------------------------------------------------------------------------
  # ORM Model domain conversion
  # ---------------------------------------------------------------------------

  Scenario: NamespacedProjectModel to_domain round-trips
    When I insert a described project "local/domain-proj" with namespace "local" and description "Domain test"
    Then converting the project to domain returns a NamespacedProject
    And the domain project name is "domain-proj"
    And the domain project namespace is "local"
    And the domain project description is "Domain test"

  Scenario: NamespacedProjectModel from_domain creates a valid model
    When I create a NamespacedProject domain model
    And I convert it to a NamespacedProjectModel via from_domain
    Then the model namespaced_name is "local/from-domain-test"
    And the model namespace is "local"

  Scenario: Project resource link index on project_name exists
    Then the project database should have an index "ix_project_resource_links_project_name" on table "project_resource_links"

  Scenario: Project resource link index on resource_id exists
    Then the project database should have an index "ix_project_resource_links_resource_id" on table "project_resource_links"

  Scenario: Project resource link composite index on project_name and alias exists
    Then the project database should have an index "ix_project_resource_links_project_alias" on table "project_resource_links"

  Scenario: Project namespace index exists
    Then the project database should have an index "ix_ns_projects_namespace" on table "ns_projects"