"""Project knowledge-pack export/import service."""

from __future__ import annotations

import json
import re
import shutil
import tempfile
import zipfile
from collections import Counter
from pathlib import Path
from typing import Any, BinaryIO

from rka.infra.ids import generate_id
from rka.models.knowledge_pack import KnowledgePackImportResult
from rka.services.base import BaseService, _now

PACK_SCHEMA_VERSION = 2
PACK_FILE_SUFFIX = ".rka-pack.zip"


# Mission A T5 (mis_01KR1Z28QW9WYXG4VV8PGYWD8G) introduced a hardcoded
# critical-categories set. Mission B Affordance E
# (mis_01KR209WY4M6WQFEXRH79KC2ZF) replaces that with a per-issue
# `severity` field on check_integrity output (see
# KnowledgePackService._SEVERITY_BY_CATEGORY). The rollback gate in
# import_pack now reads issue["severity"] == "critical" rather than
# matching against this set; the set is preserved for backward-
# compatibility with any external caller importing the symbol, but
# new code SHOULD prefer the severity field.
_CRITICAL_INTEGRITY_CATEGORIES: frozenset[str] = frozenset({
    "orphaned_entity_link_sources",
    "orphaned_entity_link_targets",
    "orphaned_claim_edge_sources",
    "orphaned_claim_edge_clusters",
})


class KnowledgePackIntegrityError(RuntimeError):
    """Raised when an imported pack fails the post-insert integrity gate.

    Carries the structured `issues` list (the same shape returned by
    KnowledgePackService.check_integrity) so the caller can surface the
    cause to the operator. The transaction is rolled back before this is
    raised; no partial state is left behind.
    """

    def __init__(self, issues: list[dict]):
        self.issues = issues
        cats = sorted({issue["category"] for issue in issues})
        super().__init__(
            "Pack import rejected: critical integrity issues — " + ", ".join(cats)
        )

# Categorized table registry — all tables with project_id MUST be listed here.
# Export validation will FAIL if any uncategorized table has data.
_TABLE_CATEGORIES: dict[str, list[str]] = {
    "core_data": [
        # MUST export, MUST import — these are the research knowledge
        "literature",
        "decisions",
        "missions",
        "journal",
        "checkpoints",
        "claims",
        "evidence_clusters",
        "claim_edges",
        "entity_links",
        "tags",
        "decision_options",
        "calibration_outcomes",
        "hooks",
        "brain_notifications",
    ],
    "derived_data": [
        # SHOULD export, can rebuild if missing
        "review_queue",
        "topics",
        "exploration_summaries",
        "context_snapshots",
        "keynodes",
        "graph_views",
        "artifacts",
        "figures",
        "bootstrap_log",
    ],
    "system": [
        # SKIP — managed by infrastructure, not per-project data
        "projects",
        "project_states",
        "project_state",
        "schema_migrations",
        "jobs",
        "kv_store",
        "embedding_metadata",
        "entity_topics",   # junction table, no project_id column
        "qa_logs",         # child of qa_sessions via session_id, no project_id
    ],
    "indexes": [
        # SKIP — rebuilt automatically (vec_*, fts_* detected by prefix)
    ],
    "bulk_logs": [
        # OPTIONAL — export only with include_logs=True
        "audit_log",
        "events",
        "qa_sessions",
        "hook_executions",
    ],
}

# Computed from categories — FK-dependency sorted insert order
_INSERT_ORDER = (
    # core_data (FK-safe order)
    "literature",
    "decisions",
    "decision_options",
    "missions",
    "journal",
    "checkpoints",
    "evidence_clusters",
    "claims",
    "claim_edges",
    "entity_links",
    "tags",
    "calibration_outcomes",
    "hooks",
    "brain_notifications",
    # derived_data
    "review_queue",
    "topics",
    "exploration_summaries",
    "context_snapshots",
    "keynodes",
    "graph_views",
    "artifacts",
    "figures",
    "bootstrap_log",
    # bulk_logs (when included)
    "audit_log",
    "events",
    "qa_sessions",
)

# Tables to export by default (core + derived)
_EXPORT_TABLES = _TABLE_CATEGORIES["core_data"] + _TABLE_CATEGORIES["derived_data"]
# All registered table names for validation
_ALL_REGISTERED = {t for tables in _TABLE_CATEGORIES.values() for t in tables}
_SELF_REFERENTIAL_TABLES: dict[str, str | list[str]] = {
    "missions": ["depends_on", "parent_mission_id"],
    "decisions": ["parent_id", "superseded_by"],
    "journal": "supersedes",
    "events": "caused_by_event",
}
_ID_ENTITY_TYPES = {
    "literature": "literature",
    "missions": "mission",
    "decisions": "decision",
    "journal": "journal",
    "checkpoints": "checkpoint",
    "claims": "claim",
    "evidence_clusters": "cluster",
    "claim_edges": "claim_edge",
    "decision_options": "decision_option",
    "calibration_outcomes": "calibration_outcome",
    "hooks": "hook",
    "hook_executions": "hook_execution",
    "brain_notifications": "brain_notification",
    "artifacts": "artifact",
    "figures": "figure",
    "exploration_summaries": "summary",
    "qa_sessions": "qa_session",
    "qa_logs": "qa_log",
    "events": "event",
    "entity_links": "link",
    "review_queue": "review_item",
    "keynodes": "keynode",
    "graph_views": "graphview",
}
_DIRECT_ID_COLUMNS = {
    "literature": ("id",),
    "missions": ("id", "depends_on", "parent_mission_id", "motivated_by_decision"),
    "decisions": ("id", "parent_id", "superseded_by", "recommended_option_id", "pi_selected_option_id"),
    "journal": ("id", "related_mission", "supersedes", "superseded_by"),
    "checkpoints": ("id", "mission_id", "linked_decision_id"),
    "claims": ("id", "source_entry_id"),
    "evidence_clusters": ("id", "research_question_id"),
    "claim_edges": ("id", "source_claim_id", "target_claim_id", "cluster_id"),
    "decision_options": ("id", "decision_id", "dominated_by"),
    "calibration_outcomes": ("id", "decision_id"),
    "hooks": ("id",),
    "hook_executions": ("id", "hook_id"),
    "brain_notifications": ("id", "hook_id"),
    "artifacts": ("id",),
    "figures": ("id", "artifact_id"),
    "exploration_summaries": ("id", "scope_id"),
    "qa_sessions": ("id",),
    "qa_logs": ("id", "session_id"),
    "events": ("id", "entity_id", "caused_by_event", "caused_by_entity"),
    "audit_log": ("entity_id",),
    "tags": ("entity_id",),
    "bootstrap_log": ("entity_id",),
    "entity_links": ("id", "source_id", "target_id"),
    "review_queue": ("id", "item_id"),
    "keynodes": ("id",),
    "graph_views": ("id",),
}
# Columns with actual FOREIGN KEY constraints in the schema.
# Unresolvable references in these columns must be NULLed to avoid FK errors.
# (Excludes "id" columns — those are PKs, not FK references.)
_FK_COLUMNS: dict[str, set[str]] = {
    "decisions": {"parent_id", "superseded_by"},
    "missions": {"depends_on", "parent_mission_id", "motivated_by_decision"},
    "journal": {"supersedes"},
    "checkpoints": {"mission_id", "linked_decision_id"},
    "claims": {"source_entry_id"},
    "evidence_clusters": {"research_question_id"},
    "claim_edges": {"source_claim_id", "target_claim_id", "cluster_id"},
    "events": {"caused_by_event"},
    "qa_logs": {"session_id"},
    "figures": {"artifact_id"},
}
_JSON_ID_COLUMNS = {
    "literature": ("related_decisions",),
    "decisions": ("related_missions", "related_literature", "related_journal"),
    "journal": ("related_decisions", "related_literature"),
    "exploration_summaries": ("source_refs",),
    "qa_logs": ("answer_structured", "sources"),
    "events": ("details",),
    "audit_log": ("details",),
    "keynodes": ("node_refs",),
    "graph_views": ("nodes", "edges"),
}


class KnowledgePackService(BaseService):
    """Export and import a full project-scoped knowledge pack."""

    async def export_pack(self, project_id: str | None = None, include_logs: bool = False) -> tuple[str, str]:
        resolved_project_id = self._resolve_project_id(project_id)
        project = await self.db.fetchone(
            "SELECT * FROM projects WHERE id = ?",
            [resolved_project_id],
        )
        if project is None:
            raise ValueError(f"Project '{resolved_project_id}' not found")

        # Dynamic validation: fail if any uncategorized table has data
        await self._validate_registry(resolved_project_id)

        project_state = await self.db.fetchone(
            "SELECT * FROM project_states WHERE project_id = ?",
            [resolved_project_id],
        )

        # Determine which tables to export
        export_tables = list(_EXPORT_TABLES)
        if include_logs:
            export_tables += _TABLE_CATEGORIES["bulk_logs"]

        # Get schema version (latest migration number)
        schema_version = await self._get_schema_version()

        table_counts: dict[str, int] = {}
        manifest: dict[str, Any] = {
            "schema_version": schema_version,
            "pack_format_version": PACK_SCHEMA_VERSION,
            "exported_at": _now(),
            "rka_version": "2.1.0",
            "categories_exported": ["core_data", "derived_data"] + (["bulk_logs"] if include_logs else []),
            "project": dict(project),
            "project_state": dict(project_state) if project_state else None,
            "tables": {},
        }

        temp_file = tempfile.NamedTemporaryFile(
            prefix=f"{self._slugify(project['name'] or resolved_project_id)}-",
            suffix=PACK_FILE_SUFFIX,
            delete=False,
        )
        temp_path = Path(temp_file.name)
        temp_file.close()

        with zipfile.ZipFile(temp_path, "w", compression=zipfile.ZIP_DEFLATED) as archive:
            for table in export_tables:
                rows = await self._export_rows_for_table(table, resolved_project_id)
                if table == "artifacts":
                    rows = self._attach_artifact_files(rows, archive)
                manifest["tables"][table] = rows
                table_counts[table] = len(rows)
            manifest["table_counts"] = table_counts
            archive.writestr("manifest.json", json.dumps(manifest, indent=2, sort_keys=True))

        filename = f"{self._slugify(project['name'] or resolved_project_id)}{PACK_FILE_SUFFIX}"
        return str(temp_path), filename

    async def import_pack(
        self,
        fileobj: BinaryIO,
        project_id: str | None = None,
        project_name: str | None = None,
    ) -> KnowledgePackImportResult:
        with zipfile.ZipFile(fileobj) as archive:
            manifest = self._load_manifest(archive)
            source_project = manifest["project"]
            source_state = manifest.get("project_state")
            source_tables: dict[str, list[dict[str, Any]]] = manifest.get("tables", {})

            target_project_id = (project_id or source_project["id"]).strip()
            target_project_name = (project_name or source_project["name"]).strip()
            target_description = source_project.get("description")
            if not target_project_id:
                raise ValueError("Imported project ID cannot be empty")
            if not target_project_name:
                raise ValueError("Imported project name cannot be empty")

            await self._assert_target_project_available(target_project_id, target_project_name)
            self._assert_no_duplicate_pack_dois(source_tables)
            tables = self._remap_tables(
                source_tables,
                source_project_id=source_project["id"],
                target_project_id=target_project_id,
            )

            artifact_root = self._artifact_import_root(target_project_id)
            created_root = False

            await self.db.execute("BEGIN")
            # Defer FK checks until commit — enables forward references between
            # tables (e.g., decisions.recommended_option_id → decision_options
            # inserted later in _INSERT_ORDER). All refs must resolve by commit;
            # genuine orphans still fail there.
            await self.db.execute("PRAGMA defer_foreign_keys = ON")
            try:
                await self._insert_row(
                    "projects",
                    {
                        "id": target_project_id,
                        "name": target_project_name,
                        "description": target_description,
                        "created_by": source_project.get("created_by"),
                        "created_at": source_project.get("created_at") or _now(),
                        "updated_at": source_project.get("updated_at") or _now(),
                    },
                )

                state_row = self._build_project_state_row(
                    source_state=source_state,
                    source_project=source_project,
                    target_project_id=target_project_id,
                    target_project_name=target_project_name,
                    target_description=target_description,
                )
                await self._insert_row("project_states", state_row)

                imported_counts: dict[str, int] = {}
                artifact_files_restored = 0

                for table in _INSERT_ORDER:
                    rows = [dict(row) for row in tables.get(table, [])]
                    rows = self._prepare_rows_for_insert(table, rows, target_project_id)

                    if table == "artifacts":
                        created_root = created_root or bool(rows)
                        rows, restored = self._restore_artifact_files(rows, archive, artifact_root)
                        artifact_files_restored += restored

                    for row in rows:
                        await self._insert_row(table, row)

                    imported_counts[table] = len(rows)

                # Defect 3 (Mission A T5): integrity gate runs INSIDE the
                # transaction so critical issues (orphaned edges / missing
                # parents introduced by the imported rows) trigger a clean
                # rollback instead of landing partial state. Affordance E
                # (Mission B): the gate now reads the per-issue `severity`
                # field rather than a hardcoded category set, so the
                # severity-vs-category mapping lives in one place
                # (_SEVERITY_BY_CATEGORY) and consumers don't need to know
                # the category list.
                integrity_issues = await self.check_integrity(target_project_id)
                critical = [
                    i for i in integrity_issues
                    if i.get("severity") == "critical"
                ]
                if critical:
                    await self.db.execute("ROLLBACK")
                    if created_root and artifact_root.exists():
                        shutil.rmtree(artifact_root.parent, ignore_errors=True)
                    raise KnowledgePackIntegrityError(critical)

                await self.db.commit()
            except KnowledgePackIntegrityError:
                # Already rolled back inside the try-block; just propagate.
                raise
            except Exception:
                await self.db.execute("ROLLBACK")
                if created_root and artifact_root.exists():
                    shutil.rmtree(artifact_root.parent, ignore_errors=True)
                raise

        # Success path. Sync indexes for the (now committed) rows + repair
        # non-critical findings so the import doesn't land with stale derived
        # counts (Brain-ratified addition during the upfront Backbrief).
        await self._sync_imported_indexes(tables)
        if any(i.get("category") == "claim_count_mismatch"
               for i in integrity_issues):
            await self._recompute_cluster_claim_counts(target_project_id)

        return KnowledgePackImportResult(
            project_id=target_project_id,
            project_name=target_project_name,
            source_project_id=source_project["id"],
            imported_counts=imported_counts,
            artifact_files_restored=artifact_files_restored,
            integrity_issues=integrity_issues,
        )

    async def _export_rows_for_table(self, table: str, project_id: str) -> list[dict[str, Any]]:
        # Safety: verify the table exists and has project_id before querying
        try:
            cols = await self.db.fetchall(f"PRAGMA table_info([{table}])")
        except Exception:
            return []
        col_names = [c["name"] for c in cols]
        if not col_names:
            return []  # table doesn't exist
        if "project_id" not in col_names:
            return []  # table has no project_id — skip silently
        return await self.db.fetchall(
            f"SELECT * FROM [{table}] WHERE project_id = ? ORDER BY rowid",
            [project_id],
        )

    def _attach_artifact_files(
        self,
        rows: list[dict[str, Any]],
        archive: zipfile.ZipFile,
    ) -> list[dict[str, Any]]:
        enriched: list[dict[str, Any]] = []
        for row in rows:
            artifact = dict(row)
            filepath = artifact.get("filepath")
            pack_file = None
            if filepath:
                path = Path(filepath)
                if path.exists() and path.is_file():
                    safe_name = self._safe_filename(path.name)
                    pack_file = f"artifacts/{artifact['id']}/{safe_name}"
                    archive.write(path, pack_file)
            artifact["pack_file"] = pack_file
            enriched.append(artifact)
        return enriched

    def _load_manifest(self, archive: zipfile.ZipFile) -> dict[str, Any]:
        try:
            raw_manifest = archive.read("manifest.json")
        except KeyError as exc:
            raise ValueError("Knowledge pack is missing manifest.json") from exc
        try:
            manifest = json.loads(raw_manifest.decode("utf-8"))
        except json.JSONDecodeError as exc:
            raise ValueError("Knowledge pack manifest is not valid JSON") from exc

        pack_format = manifest.get("pack_format_version") or manifest.get("schema_version")
        if pack_format not in (1, 2, PACK_SCHEMA_VERSION):
            raise ValueError(
                f"Unsupported knowledge pack format version: {pack_format}"
            )
        if not manifest.get("project"):
            raise ValueError("Knowledge pack manifest is missing project metadata")
        return manifest

    async def _assert_target_project_available(self, project_id: str, project_name: str) -> None:
        existing_id = await self.db.fetchone("SELECT id FROM projects WHERE id = ?", [project_id])
        if existing_id:
            raise ValueError(f"Project '{project_id}' already exists")

        existing_name = await self.db.fetchone("SELECT id FROM projects WHERE name = ?", [project_name])
        if existing_name:
            raise ValueError(
                f"Project name '{project_name}' already exists. Choose a different import name."
            )

    def _assert_no_duplicate_pack_dois(self, tables: dict[str, list[dict[str, Any]]]) -> None:
        dois = [str(row["doi"]).strip() for row in tables.get("literature", []) if row.get("doi")]
        duplicate_dois = sorted(doi for doi, count in Counter(dois).items() if count > 1)
        if duplicate_dois:
            sample = ", ".join(duplicate_dois[:5])
            raise ValueError(
                f"Knowledge pack contains duplicate literature DOI(s): {sample}"
            )

    def _remap_tables(
        self,
        tables: dict[str, list[dict[str, Any]]],
        *,
        source_project_id: str,
        target_project_id: str,
    ) -> dict[str, list[dict[str, Any]]]:
        id_map = self._build_id_map(tables)
        remapped: dict[str, list[dict[str, Any]]] = {}
        for table in tables:
            remapped[table] = [
                self._remap_row(
                    table,
                    row,
                    id_map=id_map,
                    source_project_id=source_project_id,
                    target_project_id=target_project_id,
                )
                for row in tables.get(table, [])
            ]
        return remapped

    def _build_id_map(self, tables: dict[str, list[dict[str, Any]]]) -> dict[str, str]:
        id_map: dict[str, str] = {}
        for table, entity_type in _ID_ENTITY_TYPES.items():
            for row in tables.get(table, []):
                row_id = row.get("id")
                if row_id:
                    id_map[str(row_id)] = generate_id(entity_type)
        return id_map

    def _remap_row(
        self,
        table: str,
        row: dict[str, Any],
        *,
        id_map: dict[str, str],
        source_project_id: str,
        target_project_id: str,
    ) -> dict[str, Any]:
        remapped = dict(row)
        if "project_id" in remapped:
            remapped["project_id"] = target_project_id
        if table == "qa_logs":
            remapped.pop("project_id", None)

        for column in _DIRECT_ID_COLUMNS.get(table, ()):
            if remapped.get(column):
                remapped[column] = self._rewrite_direct_ref(
                    table=table,
                    column=column,
                    value=remapped[column],
                    id_map=id_map,
                    source_project_id=source_project_id,
                    target_project_id=target_project_id,
                    scope_type=remapped.get("scope_type"),
                    entity_type=remapped.get("entity_type"),
                )

        for column in _JSON_ID_COLUMNS.get(table, ()):
            if remapped.get(column):
                remapped[column] = self._rewrite_json_refs(
                    remapped[column],
                    id_map=id_map,
                    source_project_id=source_project_id,
                    target_project_id=target_project_id,
                )

        return remapped

    def _rewrite_direct_ref(
        self,
        *,
        table: str,
        column: str,
        value: Any,
        id_map: dict[str, str],
        source_project_id: str,
        target_project_id: str,
        scope_type: str | None = None,
        entity_type: str | None = None,
    ) -> Any:
        if not isinstance(value, str):
            return value

        if table == "exploration_summaries" and column == "scope_id":
            if scope_type == "project" and value == source_project_id:
                return target_project_id
            if scope_type in {"mission", "decision", "journal", "literature", "checkpoint", "summary"}:
                return id_map.get(value, value)
            return value

        if table in {"events", "audit_log"} and column == "entity_id":
            if entity_type == "project" and value == source_project_id:
                return target_project_id

        if value == source_project_id:
            return target_project_id
        remapped = id_map.get(value)
        if remapped is not None:
            return remapped
        # Value not in id_map — if this column has a FK constraint,
        # the original ID won't exist in the target DB, so NULL it out.
        if column in _FK_COLUMNS.get(table, set()):
            return None
        return value

    def _rewrite_json_refs(
        self,
        value: Any,
        *,
        id_map: dict[str, str],
        source_project_id: str,
        target_project_id: str,
    ) -> Any:
        if not isinstance(value, str):
            return value
        try:
            payload = json.loads(value)
        except json.JSONDecodeError:
            return value
        rewritten = self._rewrite_nested_refs(payload, id_map, source_project_id, target_project_id)
        return json.dumps(rewritten)

    def _rewrite_nested_refs(
        self,
        value: Any,
        id_map: dict[str, str],
        source_project_id: str,
        target_project_id: str,
    ) -> Any:
        if isinstance(value, str):
            if value == source_project_id:
                return target_project_id
            return id_map.get(value, value)
        if isinstance(value, list):
            return [
                self._rewrite_nested_refs(item, id_map, source_project_id, target_project_id)
                for item in value
            ]
        if isinstance(value, dict):
            return {
                key: self._rewrite_nested_refs(item, id_map, source_project_id, target_project_id)
                for key, item in value.items()
            }
        return value

    def _build_project_state_row(
        self,
        source_state: dict[str, Any] | None,
        source_project: dict[str, Any],
        target_project_id: str,
        target_project_name: str,
        target_description: str | None,
    ) -> dict[str, Any]:
        phases = None
        if source_state:
            phases = source_state.get("phases_config")
        if not phases:
            phases = json.dumps([
                "literature",
                "planning",
                "data_collection",
                "implementation",
                "evaluation",
                "paper_writing",
            ])
        return {
            "project_id": target_project_id,
            "project_name": target_project_name,
            "project_description": target_description,
            "current_phase": (source_state or {}).get("current_phase"),
            "phases_config": phases,
            "summary": (source_state or {}).get("summary"),
            "blockers": (source_state or {}).get("blockers"),
            "metrics": (source_state or {}).get("metrics"),
            "created_at": (source_state or {}).get("created_at") or source_project.get("created_at") or _now(),
            "updated_at": (source_state or {}).get("updated_at") or source_project.get("updated_at") or _now(),
        }

    def _prepare_rows_for_insert(
        self,
        table: str,
        rows: list[dict[str, Any]],
        target_project_id: str,
    ) -> list[dict[str, Any]]:
        prepared = [self._rewrite_project_scope(table, row, target_project_id) for row in rows]

        dependency_keys = _SELF_REFERENTIAL_TABLES.get(table)
        if dependency_keys:
            keys = [dependency_keys] if isinstance(dependency_keys, str) else list(dependency_keys)
            for key in keys:
                prepared = self._sort_rows_by_dependency(prepared, key)

        if table == "audit_log" or table == "bootstrap_log":
            for row in prepared:
                row.pop("id", None)
        return prepared

    def _rewrite_project_scope(
        self,
        table: str,
        row: dict[str, Any],
        target_project_id: str,
    ) -> dict[str, Any]:
        rewritten = dict(row)
        if "project_id" in rewritten:
            rewritten["project_id"] = target_project_id
        if table == "qa_logs":
            rewritten.pop("project_id", None)
        return rewritten

    def _sort_rows_by_dependency(
        self,
        rows: list[dict[str, Any]],
        dependency_key: str,
    ) -> list[dict[str, Any]]:
        remaining = {str(row["id"]): dict(row) for row in rows if row.get("id")}
        ordered: list[dict[str, Any]] = []
        placed: set[str] = set()

        while remaining:
            progressed = False
            for row_id, row in list(remaining.items()):
                dependency = row.get(dependency_key)
                if not dependency or dependency in placed or dependency not in remaining:
                    ordered.append(row)
                    placed.add(row_id)
                    del remaining[row_id]
                    progressed = True
            if not progressed:
                raise ValueError(
                    f"Cannot import pack because {dependency_key} contains a cycle or unresolved reference"
                )

        return ordered

    def _restore_artifact_files(
        self,
        rows: list[dict[str, Any]],
        archive: zipfile.ZipFile,
        artifact_root: Path,
    ) -> tuple[list[dict[str, Any]], int]:
        restored = 0
        if rows:
            artifact_root.mkdir(parents=True, exist_ok=True)

        prepared: list[dict[str, Any]] = []
        for row in rows:
            artifact = dict(row)
            pack_file = artifact.get("pack_file")
            if pack_file:
                destination = artifact_root / artifact["id"] / self._safe_filename(artifact.get("filename") or "artifact.bin")
                destination.parent.mkdir(parents=True, exist_ok=True)
                try:
                    with archive.open(pack_file) as src, destination.open("wb") as dst:
                        shutil.copyfileobj(src, dst)
                    artifact["filepath"] = str(destination.resolve())
                    restored += 1
                except KeyError as exc:
                    raise ValueError(f"Knowledge pack is missing bundled artifact file '{pack_file}'") from exc
            artifact.pop("pack_file", None)
            prepared.append(artifact)
        return prepared, restored

    async def _insert_row(self, table: str, row: dict[str, Any]) -> None:
        columns = list(row.keys())
        placeholders = ", ".join("?" for _ in columns)
        column_sql = ", ".join(columns)
        await self.db.execute(
            f"INSERT INTO {table} ({column_sql}) VALUES ({placeholders})",
            [row[column] for column in columns],
        )

    async def _sync_imported_indexes(self, tables: dict[str, list[dict[str, Any]]]) -> None:
        for row in tables.get("journal", []):
            await self._sync_indexes("journal", row["id"], row)
        for row in tables.get("decisions", []):
            await self._sync_indexes("decision", row["id"], row)
        for row in tables.get("literature", []):
            await self._sync_indexes("literature", row["id"], row)
        for row in tables.get("missions", []):
            await self._sync_indexes("mission", row["id"], row)
        # Defect 3 (mis_01KR1Z28QW9WYXG4VV8PGYWD8G T5): pre-v2.3.4 the iteration
        # stopped at missions, so imported claims and clusters were invisible
        # to FTS / vec search until a manual reindex. _sync_indexes routes via
        # _FTS_CONFIG (claim + cluster present) and _EMBED_TEXT_MAP (claim
        # present; cluster vectors parked).
        for row in tables.get("claims", []):
            await self._sync_indexes("claim", row["id"], row)
        for row in tables.get("evidence_clusters", []):
            await self._sync_indexes("cluster", row["id"], row)

    async def _recompute_cluster_claim_counts(self, project_id: str) -> None:
        """Project-scoped recompute of evidence_clusters.claim_count from
        claim_edges.relation='member_of'. Mirrors migration 016 but bound to
        a single project so the import path can repair its own stale-derived
        rows without touching other projects.
        """
        await self.db.execute(
            """UPDATE evidence_clusters
               SET claim_count = (
                   SELECT COUNT(*) FROM claim_edges
                   WHERE claim_edges.cluster_id = evidence_clusters.id
                     AND claim_edges.relation = 'member_of'
                     AND claim_edges.project_id = evidence_clusters.project_id
               ),
               updated_at = ?
               WHERE project_id = ?""",
            [_now(), project_id],
        )
        await self.db.commit()

    def _artifact_import_root(self, project_id: str) -> Path:
        db_dir = Path(self.db.db_path).resolve().parent
        return db_dir / "knowledge-packs" / project_id / "artifacts"

    @staticmethod
    def _slugify(value: str) -> str:
        slug = re.sub(r"[^A-Za-z0-9._-]+", "-", value).strip("-.")
        return slug or "knowledge-pack"

    @staticmethod
    def _safe_filename(value: str) -> str:
        return Path(value).name or "artifact.bin"

    async def _validate_registry(self, project_id: str) -> None:
        """Fail if any table with project_id data exists but isn't categorized."""
        all_tables = await self.db.fetchall(
            "SELECT name FROM sqlite_master WHERE type='table'"
        )
        for row in all_tables:
            table_name = row["name"]
            if table_name.startswith(("vec_", "fts_", "sqlite_")):
                continue
            if table_name in _ALL_REGISTERED:
                continue
            # Check if table has project_id column
            cols = await self.db.fetchall(f"PRAGMA table_info({table_name})")
            col_names = [c["name"] for c in cols]
            if "project_id" not in col_names:
                continue
            # Check if table has data for this project
            count = await self.db.fetchone(
                f"SELECT COUNT(*) as c FROM [{table_name}] WHERE project_id = ?",
                [project_id],
            )
            if count and count["c"] > 0:
                raise ValueError(
                    f"Table '{table_name}' has {count['c']} rows for project {project_id} "
                    f"but is not registered in _TABLE_CATEGORIES. "
                    f"Add it to the appropriate category in knowledge_pack.py before exporting."
                )

    async def _get_schema_version(self) -> int:
        """Get the latest migration number as the schema version."""
        try:
            row = await self.db.fetchone(
                "SELECT MAX(CAST(SUBSTR(name, 1, 3) AS INTEGER)) as ver FROM schema_migrations"
            )
            return row["ver"] if row and row["ver"] else 0
        except Exception:
            return 0

    # Affordance E (Mission B / mis_01KR209WY4M6WQFEXRH79KC2ZF):
    # severity table mapping integrity-issue category → severity level. The
    # 4 orphan-class categories are critical (rollback on import); the
    # claim_count_mismatch category is warning (commit + recompute).
    # New categories added later default to "warning" — explicit migration
    # to "critical" is required if a future invariant violation should
    # block imports. Mirrors the philosophy of Mission A T5's hardcoded
    # gate (commit 716712c) but keeps severity explicit on each issue
    # so consumers don't need to know the category list.
    _SEVERITY_BY_CATEGORY: dict[str, str] = {
        "orphaned_entity_link_sources": "critical",
        "orphaned_entity_link_targets": "critical",
        "orphaned_claim_edge_sources": "critical",
        "orphaned_claim_edge_clusters": "critical",
        "claim_count_mismatch": "warning",
    }

    @classmethod
    def _severity_for(cls, category: str) -> str:
        """Return severity for the given integrity category. Defaults to
        'warning' for unknown categories so new advisory checks land
        non-blocking by default."""
        return cls._SEVERITY_BY_CATEGORY.get(category, "warning")

    async def check_integrity(self, project_id: str | None = None) -> list[dict]:
        """Verify knowledge base integrity — check for orphaned edges, missing refs, count mismatches.

        Each issue dict carries a `severity` field (`critical | warning`) so
        consumers can decide rollback vs warn-and-recompute without knowing
        the category list. Affordance E (Mission B).
        """
        pid = project_id or self.project_id
        issues: list[dict] = []

        # 1. entity_links with orphaned source_id
        orphaned_source = await self.db.fetchall(
            """SELECT el.id, el.source_id, el.source_type FROM entity_links el
               WHERE el.project_id = ?
               AND NOT EXISTS (SELECT 1 FROM journal j WHERE j.id = el.source_id)
               AND NOT EXISTS (SELECT 1 FROM decisions d WHERE d.id = el.source_id)
               AND NOT EXISTS (SELECT 1 FROM literature l WHERE l.id = el.source_id)
               AND NOT EXISTS (SELECT 1 FROM missions m WHERE m.id = el.source_id)
               AND NOT EXISTS (SELECT 1 FROM claims c WHERE c.id = el.source_id)
               AND NOT EXISTS (SELECT 1 FROM evidence_clusters ec WHERE ec.id = el.source_id)
               LIMIT 50""",
            [pid],
        )
        if orphaned_source:
            cat = "orphaned_entity_link_sources"
            issues.append({
                "category": cat,
                "severity": self._severity_for(cat),
                "count": len(orphaned_source),
                "ids": [r["id"] for r in orphaned_source[:10]],
                "description": "entity_links with source_id pointing to non-existent entities",
                "fix_action": "Delete orphaned edges or re-import missing entities",
            })

        # 2. entity_links with orphaned target_id
        orphaned_target = await self.db.fetchall(
            """SELECT el.id, el.target_id, el.target_type FROM entity_links el
               WHERE el.project_id = ?
               AND NOT EXISTS (SELECT 1 FROM journal j WHERE j.id = el.target_id)
               AND NOT EXISTS (SELECT 1 FROM decisions d WHERE d.id = el.target_id)
               AND NOT EXISTS (SELECT 1 FROM literature l WHERE l.id = el.target_id)
               AND NOT EXISTS (SELECT 1 FROM missions m WHERE m.id = el.target_id)
               AND NOT EXISTS (SELECT 1 FROM claims c WHERE c.id = el.target_id)
               AND NOT EXISTS (SELECT 1 FROM evidence_clusters ec WHERE ec.id = el.target_id)
               LIMIT 50""",
            [pid],
        )
        if orphaned_target:
            cat = "orphaned_entity_link_targets"
            issues.append({
                "category": cat,
                "severity": self._severity_for(cat),
                "count": len(orphaned_target),
                "ids": [r["id"] for r in orphaned_target[:10]],
                "description": "entity_links with target_id pointing to non-existent entities",
                "fix_action": "Delete orphaned edges or re-import missing entities",
            })

        # 3. claim_edges with orphaned source_claim_id
        orphaned_claims = await self.db.fetchall(
            """SELECT ce.id FROM claim_edges ce
               WHERE ce.project_id = ?
               AND NOT EXISTS (SELECT 1 FROM claims c WHERE c.id = ce.source_claim_id)
               LIMIT 50""",
            [pid],
        )
        if orphaned_claims:
            cat = "orphaned_claim_edge_sources"
            issues.append({
                "category": cat,
                "severity": self._severity_for(cat),
                "count": len(orphaned_claims),
                "ids": [r["id"] for r in orphaned_claims[:10]],
                "description": "claim_edges referencing non-existent claims",
                "fix_action": "Delete orphaned claim edges",
            })

        # 4. claim_edges with orphaned cluster_id
        orphaned_clusters = await self.db.fetchall(
            """SELECT ce.id FROM claim_edges ce
               WHERE ce.project_id = ? AND ce.cluster_id IS NOT NULL
               AND NOT EXISTS (SELECT 1 FROM evidence_clusters ec WHERE ec.id = ce.cluster_id)
               LIMIT 50""",
            [pid],
        )
        if orphaned_clusters:
            cat = "orphaned_claim_edge_clusters"
            issues.append({
                "category": cat,
                "severity": self._severity_for(cat),
                "count": len(orphaned_clusters),
                "ids": [r["id"] for r in orphaned_clusters[:10]],
                "description": "claim_edges referencing non-existent clusters",
                "fix_action": "Delete orphaned claim edges or re-create clusters",
            })

        # 5. evidence_clusters.claim_count mismatch
        mismatched = await self.db.fetchall(
            """SELECT ec.id, ec.claim_count,
                      (SELECT COUNT(*) FROM claim_edges ce
                       WHERE ce.cluster_id = ec.id AND ce.relation = 'member_of') as actual
               FROM evidence_clusters ec
               WHERE ec.project_id = ?
               AND ec.claim_count != (SELECT COUNT(*) FROM claim_edges ce
                                      WHERE ce.cluster_id = ec.id AND ce.relation = 'member_of')
               LIMIT 50""",
            [pid],
        )
        if mismatched:
            cat = "claim_count_mismatch"
            issues.append({
                "category": cat,
                "severity": self._severity_for(cat),
                "count": len(mismatched),
                "ids": [r["id"] for r in mismatched[:10]],
                "description": "evidence_clusters with claim_count != actual claim_edges count",
                "fix_action": "Run migration 016 to recompute claim counts",
            })

        return issues
