"""Step definitions for project database table migration tests.

Tests the NamespacedProjectModel and ProjectResourceLinkModel ORM models
and their corresponding database tables (projects, project_resource_links).
Uses an in-memory SQLite database with schema created via
``Base.metadata.create_all``.
"""

from __future__ import annotations

import json
from datetime import UTC, datetime
from typing import Any

from behave import given, then, when  # type: ignore[import-untyped]
from sqlalchemy import create_engine, event, inspect, text
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import Session, sessionmaker

from cleveragents.infrastructure.database.models import (
    Base,
    NamespacedProjectModel,
    ProjectResourceLinkModel,
    ResourceModel,
    ResourceTypeModel,
)


def _now_iso() -> str:
    """Return current UTC time as ISO-8601 string."""
    return datetime.now(tz=UTC).isoformat()


def _make_ulid(suffix: str = "0") -> str:
    """Create a deterministic 26-char fake ULID for testing."""
    return ("0" * (26 - len(suffix))) + suffix


def _set_sqlite_pragma_projects(dbapi_conn: Any, _connection_record: Any) -> None:
    """Enable FK enforcement on every new SQLite connection."""
    cursor = dbapi_conn.cursor()
    cursor.execute("PRAGMA foreign_keys = ON")
    cursor.close()


# ---------------------------------------------------------------------------
# Background
# ---------------------------------------------------------------------------


@given("a project tables in-memory database is initialized")
def step_project_tables_init_db(context: Any) -> None:
    """Create an in-memory SQLite database with all tables."""
    engine = create_engine("sqlite:///:memory:")
    event.listen(engine, "connect", _set_sqlite_pragma_projects)
    with engine.connect() as conn:
        conn.execute(text("PRAGMA foreign_keys = ON"))
        conn.commit()
    Base.metadata.create_all(engine)
    context.pt_engine = engine
    context.pt_session_factory = sessionmaker(bind=engine)
    context.pt_session = context.pt_session_factory()
    # Storage for cross-step references
    context.pt_project = None
    context.pt_link = None
    context.pt_resource = None
    context.pt_resource_id = None
    context.pt_error = None
    context.pt_domain_project = None
    context.pt_model_from_domain = None


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


@then('the project database should contain table "{table_name}"')
def step_project_table_exists(context: Any, table_name: str) -> None:
    inspector = inspect(context.pt_engine)
    tables = inspector.get_table_names()
    assert table_name in tables, f"Table {table_name} not found. Tables: {tables}"


# ---------------------------------------------------------------------------
# Index existence
# ---------------------------------------------------------------------------


@then(
    'the project database should have an index "{index_name}" on table "{table_name}"'
)
def step_project_index_exists(context: Any, index_name: str, table_name: str) -> None:
    inspector = inspect(context.pt_engine)
    indexes = inspector.get_indexes(table_name)
    index_names = [idx["name"] for idx in indexes]
    assert index_name in index_names, (
        f"Index {index_name} not found on {table_name}. Indexes: {index_names}"
    )


# ---------------------------------------------------------------------------
# Project CRUD
# ---------------------------------------------------------------------------


@when('I insert a project "{name}" with namespace "{namespace}"')
def step_insert_project(context: Any, name: str, namespace: str) -> None:
    session: Session = context.pt_session
    proj = NamespacedProjectModel(
        namespaced_name=name,
        namespace=namespace,
        tags_json="[]",
        created_at=_now_iso(),
        updated_at=_now_iso(),
    )
    session.add(proj)
    session.commit()
    context.pt_project = proj


@when(
    'I insert a described project "{name}" with namespace "{namespace}"'
    ' and description "{desc}"'
)
def step_insert_project_with_desc(
    context: Any, name: str, namespace: str, desc: str
) -> None:
    session: Session = context.pt_session
    proj = NamespacedProjectModel(
        namespaced_name=name,
        namespace=namespace,
        description=desc,
        tags_json="[]",
        created_at=_now_iso(),
        updated_at=_now_iso(),
    )
    session.add(proj)
    session.commit()
    context.pt_project = proj


@when('I insert a project "{name}" with JSON fields')
def step_insert_project_with_json(context: Any, name: str) -> None:
    session: Session = context.pt_session
    proj = NamespacedProjectModel(
        namespaced_name=name,
        namespace="local",
        invariants_json=json.dumps(["invariant-1", "invariant-2"]),
        context_policy_json=json.dumps({"max_file_size": 500000}),
        tags_json=json.dumps(["tag-a", "tag-b"]),
        created_at=_now_iso(),
        updated_at=_now_iso(),
    )
    session.add(proj)
    session.commit()
    context.pt_project = proj


@then('I can retrieve project "{name}"')
def step_retrieve_project(context: Any, name: str) -> None:
    session: Session = context.pt_session
    proj = session.get(NamespacedProjectModel, name)
    assert proj is not None, f"Project {name} not found"
    context.pt_project = proj


@then('the retrieved project namespace is "{expected}"')
def step_project_namespace(context: Any, expected: str) -> None:
    assert context.pt_project.namespace == expected


@then("the retrieved project description is empty")
def step_project_description_empty(context: Any) -> None:
    assert context.pt_project.description is None


@then('the retrieved project description is "{expected}"')
def step_project_description(context: Any, expected: str) -> None:
    assert context.pt_project.description == expected


@then('the retrieved project tags_json is "{expected}"')
def step_project_tags_json(context: Any, expected: str) -> None:
    assert context.pt_project.tags_json == expected


@then("the project invariants_json round-trips correctly")
def step_project_invariants_roundtrip(context: Any) -> None:
    data = json.loads(context.pt_project.invariants_json)
    assert data == ["invariant-1", "invariant-2"]


@then("the project context_policy_json round-trips correctly")
def step_project_context_policy_roundtrip(context: Any) -> None:
    data = json.loads(context.pt_project.context_policy_json)
    assert data == {"max_file_size": 500000}


@then("the project tags_json round-trips as a list")
def step_project_tags_roundtrip(context: Any) -> None:
    data = json.loads(context.pt_project.tags_json)
    assert data == ["tag-a", "tag-b"]


@then('inserting a duplicate project "{name}" raises an integrity error')
def step_duplicate_project_error(context: Any, name: str) -> None:
    session: Session = context.pt_session
    dup = NamespacedProjectModel(
        namespaced_name=name,
        namespace="local",
        tags_json="[]",
        created_at=_now_iso(),
        updated_at=_now_iso(),
    )
    session.add(dup)
    try:
        session.commit()
        raise AssertionError("Expected IntegrityError for duplicate project")
    except IntegrityError:
        session.rollback()


# ---------------------------------------------------------------------------
# Resource + type setup helpers for link tests
# ---------------------------------------------------------------------------


@given('a resource type "{name}" exists in project db')
def step_resource_type_exists_project(context: Any, name: str) -> None:
    session: Session = context.pt_session
    existing = session.get(ResourceTypeModel, name)
    if existing is not None:
        return
    namespace = name.split("/")[0]
    rt = ResourceTypeModel()
    rt.name = name
    rt.namespace = namespace
    rt.resource_kind = "physical"
    rt.user_addable = True
    rt.created_at = _now_iso()
    rt.updated_at = _now_iso()
    session.add(rt)
    session.commit()


@given('a resource "{name}" of type "{type_name}" exists in project db')
def step_resource_exists_project(context: Any, name: str, type_name: str) -> None:
    session: Session = context.pt_session
    # Generate a deterministic ULID from the resource name
    ulid = _make_ulid(str(abs(hash(name)) % 10**20).zfill(20))
    existing = session.get(ResourceModel, ulid)
    if existing is not None:
        context.pt_resource_id = ulid
        return
    r = ResourceModel()
    r.resource_id = ulid
    r.namespaced_name = name
    r.namespace = name.split("/")[0]
    r.type_name = type_name
    r.resource_kind = "physical"
    r.location = f"/tmp/{name}"
    r.created_at = _now_iso()
    r.updated_at = _now_iso()
    session.add(r)
    session.commit()
    context.pt_resource_id = ulid


@given('a project "{name}" exists in project db')
def step_project_exists_project(context: Any, name: str) -> None:
    session: Session = context.pt_session
    existing = session.get(NamespacedProjectModel, name)
    if existing is not None:
        return
    namespace = name.split("/")[0]
    proj = NamespacedProjectModel(
        namespaced_name=name,
        namespace=namespace,
        tags_json="[]",
        created_at=_now_iso(),
        updated_at=_now_iso(),
    )
    session.add(proj)
    session.commit()


# ---------------------------------------------------------------------------
# Project resource links
# ---------------------------------------------------------------------------


@when(
    'I create a project resource link for project "{project}" to resource "{resource}"'
)
def step_create_link(context: Any, project: str, resource: str) -> None:
    session: Session = context.pt_session
    # Resolve resource_id from namespaced_name
    res = (
        session.query(ResourceModel)
        .filter(ResourceModel.namespaced_name == resource)
        .first()
    )
    assert res is not None, f"Resource {resource} not found"
    link_id = _make_ulid(str(abs(hash(f"{project}:{resource}")) % 10**20).zfill(20))
    link = ProjectResourceLinkModel(
        link_id=link_id,
        project_name=project,
        resource_id=res.resource_id,
        read_only=False,
        created_at=_now_iso(),
    )
    session.add(link)
    session.commit()
    context.pt_link = link


@when('I create a project resource link with alias "{alias}" and read_only {ro}')
def step_create_link_with_alias(context: Any, alias: str, ro: str) -> None:
    session: Session = context.pt_session
    # Use the most recently created resource and project
    res = session.query(ResourceModel).first()
    proj = session.query(NamespacedProjectModel).first()
    assert res is not None and proj is not None
    link_id = _make_ulid(str(abs(hash(f"alias:{alias}")) % 10**20).zfill(20))
    link = ProjectResourceLinkModel(
        link_id=link_id,
        project_name=proj.namespaced_name,
        resource_id=res.resource_id,
        alias=alias,
        read_only=ro.lower() == "true",
        created_at=_now_iso(),
    )
    session.add(link)
    session.commit()
    context.pt_link = link


@then("the project resource link can be retrieved")
def step_link_retrievable(context: Any) -> None:
    session: Session = context.pt_session
    link = session.get(ProjectResourceLinkModel, context.pt_link.link_id)
    assert link is not None, "Link not found"
    context.pt_link = link


@then('the project resource link project_name is "{expected}"')
def step_link_project_name(context: Any, expected: str) -> None:
    assert context.pt_link.project_name == expected


@then("the project resource link read_only is false")
def step_link_read_only_false(context: Any) -> None:
    assert context.pt_link.read_only is False


@then("the project resource link read_only is true")
def step_link_read_only_true(context: Any) -> None:
    assert context.pt_link.read_only is True


@then('the project resource link alias is "{expected}"')
def step_link_alias(context: Any, expected: str) -> None:
    assert context.pt_link.alias == expected


@then(
    'creating a duplicate link for project "{project}" to resource "{resource}"'
    " raises an integrity error"
)
def step_duplicate_link_error(context: Any, project: str, resource: str) -> None:
    session: Session = context.pt_session
    res = (
        session.query(ResourceModel)
        .filter(ResourceModel.namespaced_name == resource)
        .first()
    )
    assert res is not None
    dup_link_id = _make_ulid("99999999999999999999999999"[:26])
    dup = ProjectResourceLinkModel(
        link_id=dup_link_id,
        project_name=project,
        resource_id=res.resource_id,
        read_only=False,
        created_at=_now_iso(),
    )
    session.add(dup)
    try:
        session.commit()
        raise AssertionError("Expected IntegrityError for duplicate link")
    except IntegrityError:
        session.rollback()


# ---------------------------------------------------------------------------
# Cascade / restrict delete tests
# ---------------------------------------------------------------------------


@given('a project resource link exists for "{project}" to "{resource}"')
def step_link_exists(context: Any, project: str, resource: str) -> None:
    session: Session = context.pt_session
    res = (
        session.query(ResourceModel)
        .filter(ResourceModel.namespaced_name == resource)
        .first()
    )
    assert res is not None
    link_id = _make_ulid(
        str(abs(hash(f"exist:{project}:{resource}")) % 10**20).zfill(20)
    )
    link = ProjectResourceLinkModel(
        link_id=link_id,
        project_name=project,
        resource_id=res.resource_id,
        read_only=False,
        created_at=_now_iso(),
    )
    session.add(link)
    session.commit()
    context.pt_link = link


@when('I delete the project "{name}"')
def step_delete_project(context: Any, name: str) -> None:
    session: Session = context.pt_session
    proj = session.get(NamespacedProjectModel, name)
    assert proj is not None
    session.delete(proj)
    session.commit()


@then('the project resource link for "{project}" should not exist')
def step_link_should_not_exist(context: Any, project: str) -> None:
    session: Session = context.pt_session
    links = (
        session.query(ProjectResourceLinkModel)
        .filter(ProjectResourceLinkModel.project_name == project)
        .all()
    )
    assert len(links) == 0, f"Expected 0 links, found {len(links)}"


@then('deleting the linked resource "{resource}" raises an integrity error')
def step_delete_linked_resource_restricted(context: Any, resource: str) -> None:
    session: Session = context.pt_session
    res = (
        session.query(ResourceModel)
        .filter(ResourceModel.namespaced_name == resource)
        .first()
    )
    assert res is not None
    session.delete(res)
    try:
        session.commit()
        raise AssertionError("Expected IntegrityError for RESTRICT delete")
    except IntegrityError:
        session.rollback()


# ---------------------------------------------------------------------------
# Domain conversion
# ---------------------------------------------------------------------------


@then("converting the project to domain returns a NamespacedProject")
def step_to_domain(context: Any) -> None:
    from cleveragents.domain.models.core.project import NamespacedProject

    domain = context.pt_project.to_domain()
    assert isinstance(domain, NamespacedProject)
    context.pt_domain_project = domain


@then('the domain project name is "{expected}"')
def step_domain_project_name(context: Any, expected: str) -> None:
    assert context.pt_domain_project.name == expected


@then('the domain project namespace is "{expected}"')
def step_domain_project_namespace(context: Any, expected: str) -> None:
    assert context.pt_domain_project.namespace == expected


@then('the domain project description is "{expected}"')
def step_domain_project_description(context: Any, expected: str) -> None:
    assert context.pt_domain_project.description == expected


@when("I create a NamespacedProject domain model")
def step_create_domain_project(context: Any) -> None:
    from cleveragents.domain.models.core.project import NamespacedProject

    context.pt_domain_project = NamespacedProject(
        name="from-domain-test",
        namespace="local",
        description="Created from domain",
    )


@when("I convert it to a NamespacedProjectModel via from_domain")
def step_convert_from_domain(context: Any) -> None:
    model = NamespacedProjectModel.from_domain(context.pt_domain_project)
    context.pt_model_from_domain = model


@then('the model namespaced_name is "{expected}"')
def step_model_namespaced_name(context: Any, expected: str) -> None:
    assert context.pt_model_from_domain.namespaced_name == expected


@then('the model namespace is "{expected}"')
def step_model_namespace(context: Any, expected: str) -> None:
    assert context.pt_model_from_domain.namespace == expected