"""Step definitions for db_migration_lifecycle.feature — link type and migration.

Link-type acceptance/rejection, migration idempotency (else-branch), orphan
cleanup during migration, and downgrade verification for the m4_004 schema
parity migration.
"""

from __future__ import annotations

from typing import Any

from alembic import command
from behave import given, then, when
from sqlalchemy import inspect as sa_inspect
from sqlalchemy import text
from sqlalchemy.exc import IntegrityError

from cleveragents.infrastructure.database.migration_runner import MigrationRunner
from features.steps.db_schema_parity_steps import _insert_test_resource

# ---------------------------------------------------------------------------
# Given — migration idempotency (link_type pre-exists)
# ---------------------------------------------------------------------------


@given("resource_links already has a link_type column without CHECK constraint")
def step_add_link_type_without_check(context: Any) -> None:
    """Add a bare link_type column so the migration else-branch is exercised."""
    with context.engine.begin() as conn:
        conn.execute(
            text(
                "ALTER TABLE resource_links "
                "ADD COLUMN link_type TEXT DEFAULT 'contains'"
            )
        )


@given("resource_links already has a link_type column with a non-contains default")
def step_add_link_type_wrong_default(context: Any) -> None:
    """Add a link_type column with wrong default so the default-fix path runs."""
    with context.engine.begin() as conn:
        conn.execute(
            text("ALTER TABLE resource_links ADD COLUMN link_type TEXT DEFAULT 'other'")
        )


# ---------------------------------------------------------------------------
# Given/When/Then — orphan cleanup during migration
# ---------------------------------------------------------------------------


@given('migrations applied up to "{revision}"')
def step_migrations_up_to(context: Any, revision: str) -> None:
    runner = MigrationRunner(context.db_url)
    with context.engine.connect() as conn:
        runner.alembic_cfg.attributes["connection"] = conn
        try:
            command.upgrade(runner.alembic_cfg, revision)
            conn.commit()
        finally:
            runner.alembic_cfg.attributes.pop("connection", None)
    context.runner = runner


@given("checkpoint_metadata contains orphan decision and resource references")
def step_insert_orphan_checkpoint_refs(context: Any) -> None:
    action_name = "local/orphan-cleanup-action"
    plan_id = "01ARZ3NDEKTSV4RRFFQ69G5FD0"
    checkpoint_id_1 = "01ARZ3NDEKTSV4RRFFQ69G5FD1"
    checkpoint_id_2 = "01ARZ3NDEKTSV4RRFFQ69G5FD2"

    with context.engine.begin() as conn:
        conn.execute(
            text(
                """
                INSERT INTO actions (
                    namespaced_name, namespace, name, description,
                    definition_of_done, strategy_actor, execution_actor,
                    created_at, updated_at
                ) VALUES (
                    :namespaced_name, :namespace, :name, :description,
                    :definition_of_done, :strategy_actor, :execution_actor,
                    :created_at, :updated_at
                )
                """
            ),
            {
                "namespaced_name": action_name,
                "namespace": "local",
                "name": "orphan-cleanup-action",
                "description": "test action",
                "definition_of_done": "test dod",
                "strategy_actor": "local/strategy",
                "execution_actor": "local/execution",
                "created_at": "2026-01-01T00:00:00",
                "updated_at": "2026-01-01T00:00:00",
            },
        )
        # Detect whether root_plan_id column exists (added by
        # m8_001_align_plans_schema which may not yet be applied when
        # this step runs at the m4_003 migration state).
        plan_columns = {
            col["name"] for col in sa_inspect(context.engine).get_columns("v3_plans")
        }
        plan_cols = (
            "plan_id, action_name, namespaced_name, namespace,"
            " description, created_at, updated_at"
        )
        plan_vals = (
            ":plan_id, :action_name, :namespaced_name, :namespace,"
            " :description, :created_at, :updated_at"
        )
        plan_params: dict[str, str] = {
            "plan_id": plan_id,
            "action_name": action_name,
            "namespaced_name": "local/orphan-plan",
            "namespace": "local",
            "description": "test plan for orphan cleanup",
            "created_at": "2026-01-01T00:00:00",
            "updated_at": "2026-01-01T00:00:00",
        }
        if "root_plan_id" in plan_columns:
            plan_cols = (
                "plan_id, root_plan_id, action_name,"
                " namespaced_name, namespace,"
                " description, created_at, updated_at"
            )
            plan_vals = (
                ":plan_id, :root_plan_id, :action_name,"
                " :namespaced_name, :namespace,"
                " :description, :created_at, :updated_at"
            )
            plan_params["root_plan_id"] = plan_id
        conn.execute(
            text(f"INSERT INTO v3_plans ({plan_cols}) VALUES ({plan_vals})"),
            plan_params,
        )
        # Checkpoint with orphan decision_id
        conn.execute(
            text(
                """
                INSERT INTO checkpoint_metadata (
                    checkpoint_id, plan_id, decision_id,
                    checkpoint_type, sandbox_ref,
                    filesystem_path, created_at
                ) VALUES (
                    :checkpoint_id, :plan_id, :decision_id,
                    :checkpoint_type, :sandbox_ref,
                    :filesystem_path, :created_at
                )
                """
            ),
            {
                "checkpoint_id": checkpoint_id_1,
                "plan_id": plan_id,
                "decision_id": "ORPHAN_DECISION_ID_NONEXIST",
                "checkpoint_type": "manual",
                "sandbox_ref": "test-ref",
                "filesystem_path": "",
                "created_at": "2026-01-01T00:00:00",
            },
        )
        # Checkpoint with orphan resource_id
        conn.execute(
            text(
                """
                INSERT INTO checkpoint_metadata (
                    checkpoint_id, plan_id, resource_id,
                    checkpoint_type, sandbox_ref,
                    filesystem_path, created_at
                ) VALUES (
                    :checkpoint_id, :plan_id, :resource_id,
                    :checkpoint_type, :sandbox_ref,
                    :filesystem_path, :created_at
                )
                """
            ),
            {
                "checkpoint_id": checkpoint_id_2,
                "plan_id": plan_id,
                "resource_id": "ORPHAN_RESOURCE_ID_NONEXIST",
                "checkpoint_type": "manual",
                "sandbox_ref": "test-ref",
                "filesystem_path": "",
                "created_at": "2026-01-01T00:00:00",
            },
        )
    context.orphan_plan_id = plan_id
    context.orphan_checkpoint_id_1 = checkpoint_id_1
    context.orphan_checkpoint_id_2 = checkpoint_id_2


@when("I upgrade to the next migration revision")
def step_upgrade_one_revision(context: Any) -> None:
    runner = context.runner
    with context.engine.connect() as conn:
        runner.alembic_cfg.attributes["connection"] = conn
        try:
            # Target m4_004 explicitly because m4_003 has multiple
            # child branches (m4_004, m5_001, m8_001_*) and "+1"
            # would cause an "Ambiguous walk" error.
            command.upgrade(
                runner.alembic_cfg,
                "m4_004_schema_parity_resource_decision_checkpoint",
            )
            conn.commit()
        finally:
            runner.alembic_cfg.attributes.pop("connection", None)


@then("the orphan decision_id values should be NULL")
def step_orphan_decision_id_null(context: Any) -> None:
    with context.engine.connect() as conn:
        row = conn.execute(
            text(
                "SELECT decision_id FROM checkpoint_metadata WHERE checkpoint_id = :cid"
            ),
            {"cid": context.orphan_checkpoint_id_1},
        ).fetchone()
    assert row is not None, (
        f"Checkpoint {context.orphan_checkpoint_id_1!r} missing after migration"
    )
    assert row[0] is None, f"Expected orphan decision_id to be NULL, got {row[0]!r}"


@then("the orphan resource_id values should be NULL")
def step_orphan_resource_id_null(context: Any) -> None:
    with context.engine.connect() as conn:
        row = conn.execute(
            text(
                "SELECT resource_id FROM checkpoint_metadata WHERE checkpoint_id = :cid"
            ),
            {"cid": context.orphan_checkpoint_id_2},
        ).fetchone()
    assert row is not None, (
        f"Checkpoint {context.orphan_checkpoint_id_2!r} missing after migration"
    )
    assert row[0] is None, f"Expected orphan resource_id to be NULL, got {row[0]!r}"


# ---------------------------------------------------------------------------
# Then — link_type acceptance/rejection
# ---------------------------------------------------------------------------


@then('resource_links should accept link_type "{link_type_value}"')
def step_resource_links_accepts_link_type(context: Any, link_type_value: str) -> None:
    with context.engine.begin() as conn:
        parent_id = f"01LINKTYPE_{link_type_value.upper()[:6]}P"
        child_id = f"01LINKTYPE_{link_type_value.upper()[:6]}C"

        for rid in (parent_id, child_id):
            _insert_test_resource(conn, rid)

        conn.execute(
            text(
                """
                INSERT INTO resource_links (parent_id, child_id, link_type, created_at)
                VALUES (:parent_id, :child_id, :link_type, :created_at)
                """
            ),
            {
                "parent_id": parent_id,
                "child_id": child_id,
                "link_type": link_type_value,
                "created_at": "2026-01-01T00:00:00",
            },
        )


@then("resource_links should reject NULL link_type")
def step_resource_links_rejects_null_link_type(context: Any) -> None:
    with context.engine.begin() as conn:
        parent_id = "01LINKNULLP_TESTPARENT"
        child_id = "01LINKNULLC_TESTCHILD0"

        for rid in (parent_id, child_id):
            _insert_test_resource(conn, rid)

        try:
            conn.execute(
                text(
                    """
                    INSERT INTO resource_links (parent_id, child_id, link_type, created_at)
                    VALUES (:parent_id, :child_id, NULL, :created_at)
                    """
                ),
                {
                    "parent_id": parent_id,
                    "child_id": child_id,
                    "created_at": "2026-01-01T00:00:00",
                },
            )
        except IntegrityError:
            return

    raise AssertionError("resource_links accepted NULL link_type")


@then('resource_links should reject link_type "{link_type_value}"')
def step_resource_links_rejects_link_type(context: Any, link_type_value: str) -> None:
    with context.engine.begin() as conn:
        parent_id = "01LINKTYPE_INVALIDP_TEST"
        child_id = "01LINKTYPE_INVALIDC_TEST"

        for rid in (parent_id, child_id):
            _insert_test_resource(conn, rid)

        try:
            conn.execute(
                text(
                    """
                    INSERT INTO resource_links (parent_id, child_id, link_type, created_at)
                    VALUES (:parent_id, :child_id, :link_type, :created_at)
                    """
                ),
                {
                    "parent_id": parent_id,
                    "child_id": child_id,
                    "link_type": link_type_value,
                    "created_at": "2026-01-01T00:00:00",
                },
            )
        except IntegrityError:
            return

    raise AssertionError(
        f"resource_links accepted invalid link_type {link_type_value!r}"
    )


@then("resource_links should reject empty string link_type")
def step_resource_links_rejects_empty_link_type(context: Any) -> None:
    with context.engine.begin() as conn:
        parent_id = "01LINKTYPE_EMPTYP_TESTXX"
        child_id = "01LINKTYPE_EMPTYC_TESTXX"

        for rid in (parent_id, child_id):
            _insert_test_resource(conn, rid)

        try:
            conn.execute(
                text(
                    """
                    INSERT INTO resource_links (parent_id, child_id, link_type, created_at)
                    VALUES (:parent_id, :child_id, :link_type, :created_at)
                    """
                ),
                {
                    "parent_id": parent_id,
                    "child_id": child_id,
                    "link_type": "",
                    "created_at": "2026-01-01T00:00:00",
                },
            )
        except IntegrityError:
            return

    raise AssertionError("resource_links accepted empty string link_type")


# ---------------------------------------------------------------------------
# When/Then — downgrade verification
# ---------------------------------------------------------------------------


@when('I downgrade to revision "{revision}"')
def step_downgrade_to_specific_revision(context: Any, revision: str) -> None:
    if not hasattr(context, "runner"):
        runner = MigrationRunner(context.db_url)
        runner.run_migrations(engine=context.engine)
        context.runner = runner

    runner = context.runner
    with context.engine.connect() as conn:
        runner.alembic_cfg.attributes["connection"] = conn
        try:
            command.downgrade(runner.alembic_cfg, revision)
            conn.commit()
        finally:
            runner.alembic_cfg.attributes.pop("connection", None)


@then('the "resource_links" table should not include "{column_name}"')
def step_table_should_not_include_column(context: Any, column_name: str) -> None:
    inspector = sa_inspect(context.engine)
    columns = {col["name"] for col in inspector.get_columns("resource_links")}
    assert column_name not in columns, (
        f"Expected column {column_name!r} to be absent from resource_links, "
        f"but found it in {columns!r}"
    )


@then('the "decisions" table should not have index "{index_name}"')
def step_decisions_should_not_have_index(context: Any, index_name: str) -> None:
    inspector = sa_inspect(context.engine)
    indexes = {idx["name"] for idx in inspector.get_indexes("decisions")}
    assert index_name not in indexes, (
        f"Expected index {index_name!r} to be absent from decisions, "
        f"but found it in {indexes!r}"
    )


@then('checkpoint_metadata should not have foreign key "{fk_name}"')
def step_checkpoint_should_not_have_fk(context: Any, fk_name: str) -> None:
    inspector = sa_inspect(context.engine)
    fks = inspector.get_foreign_keys("checkpoint_metadata")
    fk_names = {fk.get("name") for fk in fks}
    assert fk_name not in fk_names, (
        f"Expected FK {fk_name!r} to be absent from checkpoint_metadata, "
        f"but found it in {fk_names!r}"
    )


@then("checkpoint_metadata should not have SQLite triggers for FK enforcement")
def step_checkpoint_no_fk_triggers(context: Any) -> None:
    if context.engine.dialect.name != "sqlite":
        return  # Triggers are SQLite-specific; skip on other dialects.

    with context.engine.connect() as conn:
        rows = conn.execute(
            text(
                "SELECT name FROM sqlite_master "
                "WHERE type = 'trigger' AND name LIKE 'trg_checkpoint_metadata_%'"
            )
        ).fetchall()

    trigger_names = [row[0] for row in rows]
    assert not trigger_names, (
        f"Expected no trg_checkpoint_metadata_* triggers after downgrade, "
        f"found: {trigger_names!r}"
    )
