"""Supabase client wrapper for Cerefox.

Thin wrapper around supabase-py that provides a clean, typed interface
for the operations Cerefox needs. All search and retrieval goes through
the RPCs defined in rpcs.sql.
"""

from __future__ import annotations

import logging
import re
from typing import Any

from supabase import Client, create_client

from cerefox.config import Settings

logger = logging.getLogger(__name__)


class CerefoxClient:
    """Lightweight wrapper around the Supabase Python client.

    Provides typed methods for calling Cerefox RPCs and performing
    basic table operations. Does not handle embedding or chunking —
    those live in their respective modules.
    """

    def __init__(self, settings: Settings) -> None:
        self._settings = settings
        self._client: Client | None = None

    @property
    def client(self) -> Client:
        """Lazily initialize and return the Supabase client."""
        if self._client is None:
            if not self._settings.is_supabase_configured():
                raise RuntimeError(
                    "Supabase is not configured. "
                    "Set CEREFOX_SUPABASE_URL and CEREFOX_SUPABASE_KEY."
                )
            self._client = create_client(
                self._settings.supabase_url,
                self._settings.supabase_key,
            )
        return self._client

    def rpc(self, function_name: str, params: dict[str, Any]) -> list[dict[str, Any]]:
        """Call a Supabase RPC and return the result rows.

        Args:
            function_name: Name of the Postgres function (e.g. 'cerefox_hybrid_search').
            params: Parameters to pass to the function.

        Returns:
            List of result rows as dicts.

        Raises:
            RuntimeError: If the RPC call fails.
        """
        try:
            response = self.client.rpc(function_name, params).execute()
            return response.data or []
        except Exception as exc:
            logger.error("RPC %s failed: %s", function_name, exc)
            raise RuntimeError(f"RPC {function_name!r} failed: {exc}") from exc

    # ── Documents ──────────────────────────────────────────────────────────────

    def insert_document(self, data: dict[str, Any]) -> dict[str, Any]:
        """Insert a document record and return the created row."""
        try:
            response = self.client.table("cerefox_documents").insert(data).execute()
            if not response.data:
                raise RuntimeError("Insert returned no data")
            return response.data[0]
        except Exception as exc:
            logger.error("insert_document failed: %s", exc)
            raise RuntimeError(f"insert_document failed: {exc}") from exc

    def find_document_by_source_path(self, source_path: str) -> dict[str, Any] | None:
        """Return the most-recently-updated document whose source_path matches, or None."""
        try:
            response = (
                self.client.table("cerefox_documents")
                .select("*")
                .eq("source_path", source_path)
                .order("updated_at", desc=True)
                .limit(1)
                .execute()
            )
            return response.data[0] if response.data else None
        except Exception as exc:
            logger.error("find_document_by_source_path failed: %s", exc)
            raise RuntimeError(f"find_document_by_source_path failed: {exc}") from exc

    def find_document_by_title(self, title: str) -> dict[str, Any] | None:
        """Return the most-recently-updated document with an exact title match, or None."""
        try:
            response = (
                self.client.table("cerefox_documents")
                .select("*")
                .eq("title", title)
                .order("updated_at", desc=True)
                .limit(1)
                .execute()
            )
            return response.data[0] if response.data else None
        except Exception as exc:
            logger.error("find_document_by_title failed: %s", exc)
            raise RuntimeError(f"find_document_by_title failed: {exc}") from exc

    # Recognises a canonical lowercase UUID (8-4-4-4-12 hex with hyphens).
    # Case-insensitive — uppercase / mixed-case UUIDs from sloppy hand-typed
    # links still match. Anchored with ^ and $ so substrings inside a longer
    # path don't accidentally trigger the UUID short-circuit.
    _UUID_RE = re.compile(
        r"^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$",
        re.IGNORECASE,
    )

    def resolve_link(
        self, path: str, *, exclude_id: str | None = None, limit: int = 10
    ) -> list[dict[str, Any]]:
        """Best-effort lookup of documents matching a path from a markdown link.

        Tries strategies in order of specificity and returns the first tier
        that yields at least one match:

        0. **document_id match** -- if the path is a valid UUID, look up the
           document directly. Most stable (survives title changes, no
           ambiguity). If the UUID doesn't resolve to a live document, the
           method returns ``[]`` *without* falling through to fuzzy tiers --
           an explicit UUID encodes explicit intent, so a missing target
           should surface as "couldn't resolve" rather than substituting
           three loosely-related alternatives.
        1. **source_path suffix match** -- ``source_path LIKE '%/<path>'``.
           Catches `[link](docs/guides/setup.md)` against a doc whose
           source_path ends with the same suffix. Most specific path tier.
        2. **basename suffix match** -- only the final path component, e.g.
           `setup.md`. Wider net; can match multiple documents.
        3. **title-slug match** -- the basename without its extension,
           slugified, compared (case-insensitive substring) against doc
           titles. Catches `[link](setup-supabase.md)` against a doc titled
           "Setup Supabase" even when no source_path was recorded.

        Each returned row carries a ``match_method`` field indicating which
        tier produced it. Soft-deleted documents are excluded. ``exclude_id``
        suppresses the caller's own document so an article linking to itself
        is not a hit.

        Args:
            path: the path from the markdown link. May be a UUID (most
                stable), a vault-relative path like ``"docs/guides/setup.md"``,
                a bare filename like ``"setup.md"``, or a document title.
                Leading ``../`` and ``./`` components are stripped before
                path-based lookup. ``#anchor`` suffixes should be stripped
                by the caller before invoking this method.
            exclude_id: optional document UUID to filter out of the results
                (typically the document containing the link).
            limit: maximum candidates per tier (default 10).

        Returns:
            A list of `{id, title, source_path, match_method}` dicts. Empty
            list if no tier produced a match.
        """
        candidate = path.strip()
        if not candidate:
            return []

        # ── Tier 0: UUID short-circuit ────────────────────────────────────
        # Explicit document_id wins over any fuzzy match. If the target was
        # deleted or doesn't exist, return [] -- don't degrade silently into
        # similarly-named documents.
        if self._UUID_RE.match(candidate):
            if candidate == exclude_id:
                return []
            try:
                doc = self.get_document_by_id(candidate)
            except Exception as exc:
                logger.warning("resolve_link tier 0 (uuid) failed: %s", exc)
                return []
            if doc is None or doc.get("deleted_at") is not None:
                return []
            return [{
                "id": doc["id"],
                "title": doc.get("title", ""),
                "source_path": doc.get("source_path"),
                "match_method": "document_id",
            }]

        # Normalise: strip leading "./" and any "../" prefix runs. We don't
        # honour relative path semantics; we just remove the parent-dir
        # garbage so the remaining string can be matched as a vault-relative
        # path or a basename.
        normalised = re.sub(r"^(?:\.\./)+", "", candidate)
        normalised = re.sub(r"^\./", "", normalised)
        normalised = normalised.lstrip("/")
        if not normalised:
            return []

        basename = normalised.rsplit("/", 1)[-1]
        select_cols = "id, title, source_path"

        # ── Tier 1: source_path ends with the full normalised path ─────────
        try:
            t1 = (
                self.client.table("cerefox_documents")
                .select(select_cols)
                .is_("deleted_at", "null")
                .like("source_path", f"%/{normalised}")
                .order("updated_at", desc=True)
                .limit(limit)
                .execute()
            )
            rows = [r for r in (t1.data or []) if r["id"] != exclude_id]
            if rows:
                return [{**r, "match_method": "source_path_suffix"} for r in rows]
        except Exception as exc:
            logger.warning("resolve_link tier 1 failed: %s", exc)

        # ── Tier 2: source_path ends with just the basename ────────────────
        # Only if it's distinct from tier 1 (i.e. the path actually had a
        # directory component). Skips the redundant query when the user
        # wrote `[link](setup.md)` and we already searched for `%/setup.md`.
        if basename != normalised:
            try:
                t2 = (
                    self.client.table("cerefox_documents")
                    .select(select_cols)
                    .is_("deleted_at", "null")
                    .like("source_path", f"%/{basename}")
                    .order("updated_at", desc=True)
                    .limit(limit)
                    .execute()
                )
                rows = [r for r in (t2.data or []) if r["id"] != exclude_id]
                if rows:
                    return [{**r, "match_method": "basename"} for r in rows]
            except Exception as exc:
                logger.warning("resolve_link tier 2 failed: %s", exc)

        # ── Tier 3: title substring match ──────────────────────────────────
        # Strip extension, then case-insensitive substring against title.
        # Two input shapes to handle:
        #   - Slug-style basename (e.g. "setup-supabase" from setup-supabase.md):
        #     convert dashes/underscores to spaces so it matches the human
        #     title "Setup Supabase".
        #   - Already a human title (e.g. "Job Hunting - Opportunity Index"
        #     from `<Job Hunting - Opportunity Index>` markdown syntax):
        #     use literally. Naively replacing `-` would turn " - " into
        #     "   " and break the match.
        # Heuristic: if the stem contains any whitespace it's already
        # human-typed; otherwise treat it as a slug.
        stem = basename.rsplit(".", 1)[0] if "." in basename else basename
        stem = stem.strip()
        if " " in stem:
            needle = stem
        else:
            needle = stem.replace("-", " ").replace("_", " ").strip()
        if needle:
            try:
                # Postgrest .ilike is wildcard-friendly; wrap in %...%
                t3 = (
                    self.client.table("cerefox_documents")
                    .select(select_cols)
                    .is_("deleted_at", "null")
                    .ilike("title", f"%{needle}%")
                    .order("updated_at", desc=True)
                    .limit(limit)
                    .execute()
                )
                rows = [r for r in (t3.data or []) if r["id"] != exclude_id]
                if rows:
                    return [{**r, "match_method": "title_match"} for r in rows]
            except Exception as exc:
                logger.warning("resolve_link tier 3 failed: %s", exc)

        return []

    def get_document_by_hash(self, content_hash: str) -> dict[str, Any] | None:
        """Return the document with the given content hash, or None if not found."""
        try:
            response = (
                self.client.table("cerefox_documents")
                .select("*")
                .eq("content_hash", content_hash)
                .limit(1)
                .execute()
            )
            return response.data[0] if response.data else None
        except Exception as exc:
            logger.error("get_document_by_hash failed: %s", exc)
            raise RuntimeError(f"get_document_by_hash failed: {exc}") from exc

    def update_document_chunk_stats(
        self, document_id: str, chunk_count: int, total_chars: int
    ) -> None:
        """Update chunk_count and total_chars on a document after ingestion."""
        try:
            self.client.table("cerefox_documents").update(
                {"chunk_count": chunk_count, "total_chars": total_chars}
            ).eq("id", document_id).execute()
        except Exception as exc:
            logger.error("update_document_chunk_stats failed: %s", exc)
            raise RuntimeError(f"update_document_chunk_stats failed: {exc}") from exc

    def get_document_by_id(self, document_id: str) -> dict[str, Any] | None:
        """Return the document with the given ID, or None if not found."""
        try:
            response = (
                self.client.table("cerefox_documents")
                .select("*")
                .eq("id", document_id)
                .limit(1)
                .execute()
            )
            return response.data[0] if response.data else None
        except Exception as exc:
            logger.error("get_document_by_id failed: %s", exc)
            raise RuntimeError(f"get_document_by_id failed: {exc}") from exc

    def delete_document(
        self, document_id: str, author: str = "unknown", author_type: str = "user"
    ) -> None:
        """Soft-delete a document (sets deleted_at, creates audit entry).

        The document remains in the database but is excluded from search.
        Use restore_document() to undo or purge_document() to permanently delete.
        """
        self.rpc("cerefox_delete_document", {
            "p_document_id": document_id,
            "p_author": author,
            "p_author_type": author_type,
        })

    def restore_document(
        self, document_id: str, author: str = "unknown", author_type: str = "user"
    ) -> None:
        """Restore a soft-deleted document (clears deleted_at)."""
        self.rpc("cerefox_restore_document", {
            "p_document_id": document_id,
            "p_author": author,
            "p_author_type": author_type,
        })

    def purge_document(
        self, document_id: str, author: str = "unknown", author_type: str = "user"
    ) -> None:
        """Permanently delete a soft-deleted document (CASCADE). Only works on soft-deleted docs."""
        self.rpc("cerefox_purge_document", {
            "p_document_id": document_id,
            "p_author": author,
            "p_author_type": author_type,
        })

    def list_deleted_documents(self, limit: int = 50) -> list[dict[str, Any]]:
        """List soft-deleted documents (trash bin)."""
        response = self.client.table("cerefox_documents").select(
            "id, title, source, chunk_count, total_chars, review_status, deleted_at, updated_at"
        ).not_.is_("deleted_at", "null").order(
            "deleted_at", desc=True
        ).limit(limit).execute()
        return response.data or []

    def delete_chunks_for_document(self, document_id: str) -> None:
        """Delete all current chunks for a document without deleting the document itself.

        Used by delete_document cascade and direct chunk cleanup. Since update_document
        now archives chunks via the cerefox_snapshot_version RPC, this method is only
        used when permanently removing a document's current chunks (e.g., full deletion).
        """
        try:
            self.client.table("cerefox_chunks").delete().eq(
                "document_id", document_id
            ).is_("version_id", "null").execute()
        except Exception as exc:
            logger.error("delete_chunks_for_document failed: %s", exc)
            raise RuntimeError(f"delete_chunks_for_document failed: {exc}") from exc

    def update_document(self, document_id: str, data: dict[str, Any]) -> dict[str, Any]:
        """Update fields on an existing document record and return the updated row."""
        try:
            response = (
                self.client.table("cerefox_documents")
                .update(data)
                .eq("id", document_id)
                .execute()
            )
            if not response.data:
                raise RuntimeError("Update returned no data")
            return response.data[0]
        except Exception as exc:
            logger.error("update_document failed: %s", exc)
            raise RuntimeError(f"update_document failed: {exc}") from exc

    def list_documents(
        self,
        project_id: str | None = None,
        limit: int = 50,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        """List documents, optionally filtered by project (M2M junction)."""
        try:
            query = (
                self.client.table("cerefox_documents")
                .select(
                    "id, title, source, source_path, content_hash, metadata, chunk_count, total_chars, review_status, created_at, updated_at"
                )
                .is_("deleted_at", "null")
                .order("updated_at", desc=True)
            )
            if project_id:
                # Resolve document IDs from the M2M junction table.
                jp = (
                    self.client.table("cerefox_document_projects")
                    .select("document_id")
                    .eq("project_id", project_id)
                    .execute()
                )
                doc_ids = [r["document_id"] for r in (jp.data or [])]
                if not doc_ids:
                    return []
                query = query.in_("id", doc_ids)
            return query.range(offset, offset + limit - 1).execute().data or []
        except Exception as exc:
            logger.error("list_documents failed: %s", exc)
            raise RuntimeError(f"list_documents failed: {exc}") from exc

    def count_documents_for_project(self, project_id: str) -> int:
        """Count ACTIVE (not soft-deleted) documents assigned to a project.

        Used by the paginated ``/projects/{id}/documents`` endpoint so the
        frontend knows the total page count without fetching every row.
        """
        try:
            # Resolve all document IDs in this project from the junction.
            jp = (
                self.client.table("cerefox_document_projects")
                .select("document_id")
                .eq("project_id", project_id)
                .execute()
            )
            doc_ids = [r["document_id"] for r in (jp.data or [])]
            if not doc_ids:
                return 0
            # PostgREST count via head-only request: fast, no row payload.
            resp = (
                self.client.table("cerefox_documents")
                .select("id", count="exact", head=True)
                .is_("deleted_at", "null")
                .in_("id", doc_ids)
                .execute()
            )
            return resp.count or 0
        except Exception as exc:
            logger.error("count_documents_for_project failed: %s", exc)
            return 0

    def list_all_documents(self, batch_size: int = 200) -> list[dict[str, Any]]:
        """Return every document, paginating internally to avoid the default limit.

        Used by backup to ensure no documents are silently omitted.
        """
        results: list[dict[str, Any]] = []
        offset = 0
        while True:
            try:
                page = (
                    self.client.table("cerefox_documents")
                    .select(
                        "id, title, source, source_path, content_hash, metadata, "
                        "chunk_count, total_chars, created_at, updated_at"
                    )
                    .order("created_at")
                    .range(offset, offset + batch_size - 1)
                    .execute()
                ).data or []
                results.extend(page)
                if len(page) < batch_size:
                    break
                offset += batch_size
            except Exception as exc:
                logger.error("list_all_documents failed at offset %d: %s", offset, exc)
                raise RuntimeError(f"list_all_documents failed: {exc}") from exc
        return results

    # ── Document ↔ Project (M2M) ───────────────────────────────────────────────

    def get_document_project_ids(self, document_id: str) -> list[str]:
        """Return all project UUIDs currently assigned to a document."""
        try:
            response = (
                self.client.table("cerefox_document_projects")
                .select("project_id")
                .eq("document_id", document_id)
                .execute()
            )
            return [r["project_id"] for r in (response.data or [])]
        except Exception as exc:
            logger.error("get_document_project_ids failed: %s", exc)
            raise RuntimeError(f"get_document_project_ids failed: {exc}") from exc

    def assign_document_projects(self, document_id: str, project_ids: list[str]) -> None:
        """Replace all project associations for a document.

        Deletes existing associations then inserts the new set.
        Pass an empty list to remove the document from all projects.

        DESTRUCTIVE. Use ``add_document_to_projects`` for non-destructive
        additions that preserve existing memberships (the right choice when
        the caller is updating content and only intends to ensure one project
        membership exists — see issue #38).
        """
        try:
            self.client.table("cerefox_document_projects").delete().eq(
                "document_id", document_id
            ).execute()
            if project_ids:
                rows = [{"document_id": document_id, "project_id": pid} for pid in project_ids]
                self.client.table("cerefox_document_projects").insert(rows).execute()
        except Exception as exc:
            logger.error("assign_document_projects failed: %s", exc)
            raise RuntimeError(f"assign_document_projects failed: {exc}") from exc

    def set_document_projects(
        self,
        document_id: str,
        project_names: list[str],
        *,
        author: str = "unknown",
        author_type: str = "user",
    ) -> dict[str, Any]:
        """Set the document's project memberships to exactly ``project_names``.

        Resolves each name to a project_id (creating projects that don't exist),
        then performs a DELETE-then-INSERT replace on ``cerefox_document_projects``.
        Empty list = remove from all projects.

        Logged in the audit log as ``update-metadata`` (project membership is
        document metadata, not content). The agent-facing
        ``cerefox_set_document_projects`` MCP tool wraps this method.

        Returns:
            Dict with ``document_id``, ``project_ids`` (resolved UUIDs in order),
            and ``project_names`` (the names, mirrored back).

        Raises:
            ValueError: If the document doesn't exist (or is soft-deleted).
        """
        # Verify the document exists and isn't soft-deleted.
        doc = self.get_document_by_id(document_id)
        if doc is None:
            raise ValueError(f"Document not found: {document_id}")

        # Strip empties; preserve order; dedup case-insensitively to avoid
        # creating the same project twice when an agent passes ["Foo", "foo"].
        seen_lower: set[str] = set()
        clean_names: list[str] = []
        for n in project_names:
            if not isinstance(n, str) or not n.strip():
                continue
            stripped = n.strip()
            key = stripped.lower()
            if key in seen_lower:
                continue
            seen_lower.add(key)
            clean_names.append(stripped)

        # Resolve names to UUIDs (create missing projects).
        resolved_ids: list[str] = []
        for name in clean_names:
            project = self.get_or_create_project(name)
            resolved_ids.append(project["id"])

        # Destructive replace via the existing primitive.
        self.assign_document_projects(document_id, resolved_ids)

        # Audit entry. update-metadata semantics — project membership is part
        # of the document's structured metadata, not its content.
        try:
            self.create_audit_entry(
                operation="update-metadata",
                author=author,
                author_type=author_type,
                document_id=document_id,
                description=(
                    f"Set document projects to [{', '.join(clean_names)}]"
                    if clean_names
                    else "Cleared all project memberships"
                ),
            )
        except Exception as exc:
            logger.warning("set_document_projects: audit entry failed: %s", exc)

        return {
            "document_id": document_id,
            "project_ids": resolved_ids,
            "project_names": clean_names,
        }

    def add_document_to_projects(self, document_id: str, project_ids: list[str]) -> None:
        """Non-destructively add project memberships. Idempotent.

        Inserts rows for any ``(document_id, project_id)`` pair not already
        present. Existing memberships are preserved. Pass an empty list to
        no-op.

        This is the right primitive for content-update flows where a single
        ``project_name`` was supplied: the intent is "ensure this membership
        exists" not "set the full membership list". See ``assign_document_projects``
        for the destructive replace path used by the web UI's explicit
        project-edit form.
        """
        if not project_ids:
            return
        try:
            existing = set(self.get_document_project_ids(document_id))
            new_ids = [pid for pid in project_ids if pid and pid not in existing]
            if not new_ids:
                return  # All requested memberships already exist — idempotent
            rows = [{"document_id": document_id, "project_id": pid} for pid in new_ids]
            self.client.table("cerefox_document_projects").insert(rows).execute()
        except Exception as exc:
            logger.error("add_document_to_projects failed: %s", exc)
            raise RuntimeError(f"add_document_to_projects failed: {exc}") from exc

    def get_projects_for_documents(
        self,
        doc_ids: list[str],
        projects: list[dict[str, Any]],
    ) -> dict[str, list[dict[str, Any]]]:
        """Return a {doc_id: [project_dicts]} map for a batch of document IDs.

        Performs a single junction-table query rather than N individual lookups.
        Degrades gracefully (returns empty lists) on error so display still works.
        """
        result: dict[str, list[dict[str, Any]]] = {did: [] for did in doc_ids}
        if not doc_ids:
            return result
        projects_by_id = {p["id"]: p for p in projects}
        try:
            resp = (
                self.client.table("cerefox_document_projects")
                .select("document_id, project_id")
                .in_("document_id", doc_ids)
                .execute()
            )
            for row in (resp.data or []):
                did = row["document_id"]
                pid = row["project_id"]
                if did in result and pid in projects_by_id:
                    result[did].append(projects_by_id[pid])
        except Exception as exc:
            logger.error("get_projects_for_documents failed: %s", exc)
        return result

    def get_project_doc_counts(
        self, project_ids: list[str],
    ) -> tuple[dict[str, int], dict[str, int]]:
        """Return per-project active and deleted document counts.

        Returns a tuple ``(active_counts, deleted_counts)`` keyed by project_id.
        Both maps include every requested project_id (zero when no docs match).

        Joins the M2M junction table with ``cerefox_documents`` so soft-deleted
        documents are counted separately. Previously this method counted all
        rows on the junction regardless of the linked document's ``deleted_at``,
        which inflated the dashboard count after deletes.

        Degrades gracefully to all-zeros on error so the dashboard still renders.
        """
        active: dict[str, int] = {pid: 0 for pid in project_ids}
        deleted: dict[str, int] = {pid: 0 for pid in project_ids}
        if not project_ids:
            return active, deleted
        try:
            # PostgREST resource-embedding: get junction rows joined to docs so
            # we can read each linked document's deleted_at in one round-trip.
            resp = (
                self.client.table("cerefox_document_projects")
                .select("project_id, cerefox_documents(deleted_at)")
                .in_("project_id", project_ids)
                .execute()
            )
            for row in (resp.data or []):
                pid = row.get("project_id")
                if pid not in active:
                    continue
                # The embedded resource is a dict; deleted_at is None for active docs.
                doc = row.get("cerefox_documents") or {}
                if doc.get("deleted_at") is None:
                    active[pid] += 1
                else:
                    deleted[pid] += 1
        except Exception as exc:
            logger.error("get_project_doc_counts failed: %s", exc)
        return active, deleted

    # ── Chunks ─────────────────────────────────────────────────────────────────

    def insert_chunks(self, chunks: list[dict[str, Any]]) -> list[dict[str, Any]]:
        """Insert multiple chunk records and return the created rows."""
        if not chunks:
            return []
        try:
            response = self.client.table("cerefox_chunks").insert(chunks).execute()
            return response.data or []
        except Exception as exc:
            logger.error("insert_chunks failed: %s", exc)
            raise RuntimeError(f"insert_chunks failed: {exc}") from exc

    def ingest_document_rpc(
        self,
        *,
        document_id: str | None = None,
        title: str = "Untitled",
        source: str = "agent",
        source_path: str | None = None,
        content_hash: str = "",
        metadata: dict | None = None,
        review_status: str = "approved",
        chunks: list[dict[str, Any]] | None = None,
        author: str = "unknown",
        author_type: str = "user",
        source_label: str = "manual",
        retention_hours: int = 48,
        cleanup_enabled: bool = True,
    ) -> dict[str, Any]:
        """Ingest a document via the cerefox_ingest_document RPC.

        Handles both create (document_id=None) and update (document_id=UUID).
        Chunks must include embedding as a list of floats.

        Returns dict with: document_id, chunk_count, total_chars, operation, version_id.
        """
        # Convert chunk embeddings to plain lists for JSONB serialization
        chunk_data = []
        for c in (chunks or []):
            entry = {
                "chunk_index": c.get("chunk_index", 0),
                "heading_path": c.get("heading_path", []),
                "heading_level": c.get("heading_level"),
                "title": c.get("title", ""),
                "content": c.get("content", ""),
                "char_count": c.get("char_count", 0),
                "embedding": list(c["embedding_primary"]),
                "embedder": c.get("embedder_primary", "text-embedding-3-small"),
            }
            chunk_data.append(entry)

        params = {
            "p_document_id": document_id,
            "p_title": title,
            "p_source": source,
            "p_source_path": source_path,
            "p_content_hash": content_hash,
            "p_metadata": metadata or {},
            "p_review_status": review_status,
            "p_chunks": chunk_data,
            "p_author": author,
            "p_author_type": author_type,
            "p_source_label": source_label,
            "p_retention_hours": retention_hours,
            "p_cleanup_enabled": cleanup_enabled,
        }

        rows = self.rpc("cerefox_ingest_document", params)
        if not rows:
            raise RuntimeError("cerefox_ingest_document returned no data")
        return rows[0]

    def list_chunks_for_document(self, document_id: str) -> list[dict[str, Any]]:
        """Return current chunks for a document (version_id IS NULL), ordered by chunk_index."""
        try:
            response = (
                self.client.table("cerefox_chunks")
                .select(
                    "id, document_id, chunk_index, heading_path, heading_level, "
                    "title, content, char_count, version_id, "
                    "embedding_primary, embedding_upgrade, embedder_primary, embedder_upgrade, "
                    "created_at"
                )
                .eq("document_id", document_id)
                .is_("version_id", "null")
                .order("chunk_index")
                .execute()
            )
            return response.data or []
        except Exception as exc:
            logger.error("list_chunks_for_document failed: %s", exc)
            raise RuntimeError(f"list_chunks_for_document failed: {exc}") from exc

    def list_all_chunks(
        self,
        embedder_not: str | None = None,
        batch_size: int = 200,
    ) -> list[dict[str, Any]]:
        """Return all current chunks (version_id IS NULL), optionally filtered by embedder.

        Used by ``cerefox reindex`` to find current chunks that need re-embedding.
        Archived chunks are excluded — they retain their original embeddings.

        Args:
            embedder_not: If set, exclude chunks where ``embedder_primary`` equals
                this string (i.e. already up to date).
            batch_size: Page size for pagination (max Supabase returns per request).
        """
        results: list[dict[str, Any]] = []
        offset = 0
        while True:
            try:
                query = (
                    self.client.table("cerefox_chunks")
                    .select("id, document_id, content, embedder_primary")
                    .is_("version_id", "null")
                    .order("id")
                    .range(offset, offset + batch_size - 1)
                )
                if embedder_not:
                    query = query.neq("embedder_primary", embedder_not)
                page = query.execute().data or []
                results.extend(page)
                if len(page) < batch_size:
                    break
                offset += batch_size
            except Exception as exc:
                logger.error("list_all_chunks failed at offset %d: %s", offset, exc)
                raise RuntimeError(f"list_all_chunks failed: {exc}") from exc
        return results

    def update_chunk_embedding(
        self,
        chunk_id: str,
        embedding: list[float],
        embedder_name: str,
    ) -> None:
        """Update the primary embedding and embedder label for a single chunk."""
        try:
            self.client.table("cerefox_chunks").update(
                {"embedding_primary": embedding, "embedder_primary": embedder_name}
            ).eq("id", chunk_id).execute()
        except Exception as exc:
            logger.error("update_chunk_embedding failed for chunk %s: %s", chunk_id, exc)
            raise RuntimeError(f"update_chunk_embedding failed: {exc}") from exc

    def update_chunk_fts(self, document_id: str, new_title: str) -> None:
        """Refresh the FTS tsvector for all current chunks of a document.

        Called when a document's title changes without a content change, so
        the FTS index stays consistent with the new title (weight A).
        """
        self.rpc("cerefox_update_chunk_fts", {
            "p_document_id": document_id,
            "p_new_title": new_title,
        })

    def list_all_documents_basic(self) -> list[dict[str, Any]]:
        """Return id and title for all non-deleted documents.

        Used by the reindex command to build a document_id → title map,
        so chunks can be re-embedded with the correct title prefix.
        """
        try:
            response = (
                self.client.table("cerefox_documents")
                .select("id, title")
                .is_("deleted_at", "null")
                .execute()
            )
            return response.data or []
        except Exception as exc:
            logger.error("list_all_documents_basic failed: %s", exc)
            raise RuntimeError(f"list_all_documents_basic failed: {exc}") from exc

    # ── Document versions ───────────────────────────────────────────────────────

    def snapshot_version(
        self,
        document_id: str,
        source: str = "manual",
        retention_hours: int = 48,
        cleanup_enabled: bool = True,
    ) -> dict[str, Any]:
        """Archive current chunks and create a version record via RPC.

        Calls cerefox_snapshot_version which atomically:
        1. Creates a version row in cerefox_document_versions.
        2. Sets version_id on all current chunks (marking them as archived).
        3. Deletes versions older than retention_hours (always keeps the newest).
           Skips versions with archived=true. Skips cleanup if cleanup_enabled=false.

        Returns a dict with version_id, version_number, chunk_count, total_chars.
        """
        rows = self.rpc(
            "cerefox_snapshot_version",
            {
                "p_document_id": document_id,
                "p_source": source,
                "p_retention_hours": retention_hours,
                "p_cleanup_enabled": cleanup_enabled,
            },
        )
        if not rows:
            raise RuntimeError("cerefox_snapshot_version returned no data")
        return rows[0]

    def get_document_content(
        self,
        document_id: str,
        version_id: str | None = None,
    ) -> dict[str, Any] | None:
        """Return full reconstructed content of a document (current or archived version).

        Args:
            document_id: UUID of the document.
            version_id: UUID of an archived version, or None for the current version.

        Returns:
            Dict with document_id, doc_title, doc_source, doc_metadata, version_id,
            full_content, chunk_count, total_chars, created_at — or None if not found.
        """
        rows = self.rpc(
            "cerefox_get_document",
            {
                "p_document_id": document_id,
                "p_version_id": version_id,
            },
        )
        return rows[0] if rows else None

    def list_document_versions(self, document_id: str) -> list[dict[str, Any]]:
        """Return all archived versions for a document, newest first.

        Each row contains version_id, version_number, source, chunk_count,
        total_chars, created_at.  Pass version_id to get_document_content()
        to retrieve the full content of a specific version.
        """
        return self.rpc(
            "cerefox_list_document_versions",
            {"p_document_id": document_id},
        )

    # ── Projects ───────────────────────────────────────────────────────────────

    def get_or_create_project(self, name: str, description: str = "") -> dict[str, Any]:
        """Return an existing project by name, or create it if it doesn't exist."""
        try:
            response = (
                self.client.table("cerefox_projects")
                .select("*")
                .eq("name", name)
                .limit(1)
                .execute()
            )
            if response.data:
                return response.data[0]
            # Create it
            create_resp = (
                self.client.table("cerefox_projects")
                .insert({"name": name, "description": description})
                .execute()
            )
            if not create_resp.data:
                raise RuntimeError("Project creation returned no data")
            return create_resp.data[0]
        except RuntimeError:
            raise
        except Exception as exc:
            logger.error("get_or_create_project failed: %s", exc)
            raise RuntimeError(f"get_or_create_project failed: {exc}") from exc

    def list_projects(self) -> list[dict[str, Any]]:
        """Return all projects ordered by name."""
        try:
            response = (
                self.client.table("cerefox_projects")
                .select("*")
                .order("name")
                .execute()
            )
            return response.data or []
        except Exception as exc:
            logger.error("list_projects failed: %s", exc)
            raise RuntimeError(f"list_projects failed: {exc}") from exc

    def get_project_by_id(self, project_id: str) -> dict[str, Any] | None:
        """Return a project by ID, or None if not found."""
        try:
            response = (
                self.client.table("cerefox_projects")
                .select("*")
                .eq("id", project_id)
                .limit(1)
                .execute()
            )
            return response.data[0] if response.data else None
        except Exception as exc:
            logger.error("get_project_by_id failed: %s", exc)
            raise RuntimeError(f"get_project_by_id failed: {exc}") from exc

    def create_project(self, name: str, description: str = "") -> dict[str, Any]:
        """Create a new project and return the created row."""
        try:
            response = (
                self.client.table("cerefox_projects")
                .insert({"name": name, "description": description})
                .execute()
            )
            if not response.data:
                raise RuntimeError("Project creation returned no data")
            return response.data[0]
        except RuntimeError:
            raise
        except Exception as exc:
            logger.error("create_project failed: %s", exc)
            raise RuntimeError(f"create_project failed: {exc}") from exc

    def update_project(self, project_id: str, data: dict[str, Any]) -> dict[str, Any]:
        """Update fields on an existing project and return the updated row."""
        try:
            response = (
                self.client.table("cerefox_projects")
                .update(data)
                .eq("id", project_id)
                .execute()
            )
            if not response.data:
                raise RuntimeError("Project update returned no data")
            return response.data[0]
        except RuntimeError:
            raise
        except Exception as exc:
            logger.error("update_project failed: %s", exc)
            raise RuntimeError(f"update_project failed: {exc}") from exc

    def delete_project(self, project_id: str) -> None:
        """Delete a project by ID. Documents assigned to it are not deleted."""
        try:
            self.client.table("cerefox_projects").delete().eq("id", project_id).execute()
        except Exception as exc:
            logger.error("delete_project failed: %s", exc)
            raise RuntimeError(f"delete_project failed: {exc}") from exc

    def count_documents(self, project_id: str | None = None) -> int:
        """Return the total number of documents, optionally filtered by project."""
        try:
            if project_id:
                # Count via junction table for M2M project filter.
                response = (
                    self.client.table("cerefox_document_projects")
                    .select("document_id", count="exact")
                    .eq("project_id", project_id)
                    .limit(1)
                    .execute()
                )
                return response.count or 0
            query = self.client.table("cerefox_documents").select("id", count="exact").limit(1)
            response = query.execute()
            return response.count or 0
        except Exception as exc:
            logger.error("count_documents failed: %s", exc)
            raise RuntimeError(f"count_documents failed: {exc}") from exc

    # ── Metadata keys ──────────────────────────────────────────────────────────

    def list_metadata_keys(self) -> list[dict[str, Any]]:
        """Return metadata keys derived from actual doc_metadata across all documents.

        Each row has: key (str), doc_count (int), example_values (list[str]).
        """
        return self.rpc("cerefox_list_metadata_keys", {})

    # ── Search (convenience wrappers around RPCs) ──────────────────────────────

    def hybrid_search(
        self,
        query_text: str,
        query_embedding: list[float],
        match_count: int = 10,
        alpha: float = 0.7,
        use_upgrade: bool = False,
        project_id: str | None = None,
        min_score: float = 0.0,
        metadata_filter: dict | None = None,
    ) -> list[dict[str, Any]]:
        """Hybrid FTS + semantic search."""
        params: dict[str, Any] = {
            "p_query_text": query_text,
            "p_query_embedding": query_embedding,
            "p_match_count": match_count,
            "p_alpha": alpha,
            "p_use_upgrade": use_upgrade,
            "p_project_id": project_id,
            "p_min_score": min_score,
        }
        if metadata_filter is not None:
            params["p_metadata_filter"] = metadata_filter
        return self.rpc("cerefox_hybrid_search", params)

    def fts_search(
        self,
        query_text: str,
        match_count: int = 10,
        project_id: str | None = None,
        metadata_filter: dict | None = None,
    ) -> list[dict[str, Any]]:
        """Full-text keyword search."""
        params: dict[str, Any] = {
            "p_query_text": query_text,
            "p_match_count": match_count,
            "p_project_id": project_id,
        }
        if metadata_filter is not None:
            params["p_metadata_filter"] = metadata_filter
        return self.rpc("cerefox_fts_search", params)

    def semantic_search(
        self,
        query_embedding: list[float],
        match_count: int = 10,
        use_upgrade: bool = False,
        project_id: str | None = None,
        metadata_filter: dict | None = None,
    ) -> list[dict[str, Any]]:
        """Pure vector similarity search."""
        params: dict[str, Any] = {
            "p_query_embedding": query_embedding,
            "p_match_count": match_count,
            "p_use_upgrade": use_upgrade,
            "p_project_id": project_id,
        }
        if metadata_filter is not None:
            params["p_metadata_filter"] = metadata_filter
        return self.rpc("cerefox_semantic_search", params)

    def search_docs(
        self,
        query_text: str,
        query_embedding: list[float],
        match_count: int = 5,
        alpha: float = 0.7,
        project_id: str | None = None,
        min_score: float = 0.0,
        metadata_filter: dict | None = None,
    ) -> list[dict[str, Any]]:
        """Document-level hybrid search — deduplicates by document, returns content.

        Small-to-big threshold and context window are configured in the RPC
        (see rpcs.sql — cerefox_search_docs defaults). They are not exposed here
        because they are system-level tuning params, not per-call options.
        """
        params: dict[str, Any] = {
            "p_query_text": query_text,
            "p_query_embedding": query_embedding,
            "p_match_count": match_count,
            "p_alpha": alpha,
            "p_project_id": project_id,
            "p_min_score": min_score,
        }
        if metadata_filter is not None:
            params["p_metadata_filter"] = metadata_filter
        return self.rpc("cerefox_search_docs", params)

    def reconstruct_doc(self, document_id: str) -> dict[str, Any] | None:
        """Reconstruct the full markdown content of a document from its chunks."""
        rows = self.rpc("cerefox_reconstruct_doc", {"p_document_id": document_id})
        return rows[0] if rows else None

    def context_expand(
        self, chunk_ids: list[str], window_size: int = 1
    ) -> list[dict[str, Any]]:
        """Expand chunk IDs to include neighbouring chunks (small-to-big retrieval).

        Returns seed chunks plus up to *window_size* chunks before and after each
        seed within the same document, ordered by document + chunk_index.
        The ``is_seed`` field is True for the original result chunks.
        """
        return self.rpc(
            "cerefox_context_expand",
            {"p_chunk_ids": chunk_ids, "p_window_size": window_size},
        )

    def list_projects_rpc(self) -> list[dict[str, Any]]:
        """Call cerefox_list_projects RPC. Returns all projects sorted by name."""
        return self.rpc("cerefox_list_projects", {})

    def metadata_search(
        self,
        metadata_filter: dict,
        project_id: str | None = None,
        updated_since: str | None = None,
        created_since: str | None = None,
        limit: int = 10,
        include_content: bool = False,
        max_bytes: int | None = None,
    ) -> list[dict[str, Any]]:
        """Call cerefox_metadata_search RPC.

        Args:
            metadata_filter: JSONB containment filter (AND semantics).
            project_id: Optional project UUID.
            updated_since: ISO-8601 timestamp lower bound for updated_at.
            created_since: ISO-8601 timestamp lower bound for created_at.
            limit: Max results (default 10).
            include_content: Reconstruct full text for each doc.
            max_bytes: Byte budget for accumulated content (None = no limit).
        """
        params: dict[str, Any] = {
            "p_metadata_filter": metadata_filter,
            "p_project_id": project_id,
            "p_updated_since": updated_since,
            "p_created_since": created_since,
            "p_limit": limit,
            "p_include_content": include_content,
        }
        if max_bytes is not None:
            params["p_max_bytes"] = max_bytes
        return self.rpc("cerefox_metadata_search", params)

    # ── Usage tracking ────────────────────────────────────────────────────────

    def get_config(self, key: str) -> str | None:
        """Read a config value from cerefox_config."""
        rows = self.rpc("cerefox_get_config", {"p_key": key})
        # RPC returns a scalar wrapped in a list by the Supabase client
        if isinstance(rows, str):
            return rows
        if isinstance(rows, list) and rows:
            return rows[0] if isinstance(rows[0], str) else None
        return rows if isinstance(rows, str) else None

    def set_config(self, key: str, value: str) -> None:
        """Write a config value to cerefox_config (validated against allowlist)."""
        self.rpc("cerefox_set_config", {"p_key": key, "p_value": value})

    def log_usage(
        self,
        operation: str,
        access_path: str,
        requestor: str | None = None,
        document_id: str | None = None,
        project_id: str | None = None,
        query_text: str | None = None,
        result_count: int | None = None,
        extra: dict | None = None,
    ) -> None:
        """Log a usage entry (no-op if tracking is disabled in config)."""
        try:
            self.rpc("cerefox_log_usage", {
                "p_operation": operation,
                "p_access_path": access_path,
                "p_requestor": requestor,
                "p_document_id": document_id,
                "p_project_id": project_id,
                "p_query_text": query_text,
                "p_result_count": result_count,
                "p_extra": extra or {},
            })
        except Exception:
            pass  # fire-and-forget; never block on usage logging failure

    def list_usage_log(
        self,
        start: str | None = None,
        end: str | None = None,
        operation: str | None = None,
        access_path: str | None = None,
        requestor: str | None = None,
        project_id: str | None = None,
        limit: int = 100,
    ) -> list[dict[str, Any]]:
        """Query usage log entries with optional filters."""
        params: dict[str, Any] = {"p_limit": limit}
        if start is not None:
            params["p_start"] = start
        if end is not None:
            params["p_end"] = end
        if operation is not None:
            params["p_operation"] = operation
        if access_path is not None:
            params["p_access_path"] = access_path
        if requestor is not None:
            params["p_requestor"] = requestor
        if project_id is not None:
            params["p_project_id"] = project_id
        return self.rpc("cerefox_list_usage_log", params)

    def usage_summary(
        self,
        start: str | None = None,
        end: str | None = None,
        project_id: str | None = None,
        access_path: str | None = None,
    ) -> dict[str, Any]:
        """Get aggregated usage statistics."""
        params: dict[str, Any] = {}
        if start is not None:
            params["p_start"] = start
        if end is not None:
            params["p_end"] = end
        if project_id is not None:
            params["p_project_id"] = project_id
        if access_path is not None:
            params["p_access_path"] = access_path
        result = self.rpc("cerefox_usage_summary", params)
        if isinstance(result, list) and result:
            return result[0] if isinstance(result[0], dict) else {}
        if isinstance(result, dict):
            return result
        return {}

    def save_note(
        self,
        title: str,
        content: str,
        source: str = "agent",
        project_id: str | None = None,
        metadata: dict | None = None,
    ) -> dict[str, Any]:
        """Call cerefox_save_note to quickly capture a document record.

        This is the agent write path.  Content is NOT chunked or embedded
        server-side — use :class:`~cerefox.ingestion.pipeline.IngestionPipeline`
        for full ingest with search support.
        """
        rows = self.rpc(
            "cerefox_save_note",
            {
                "p_title": title,
                "p_content": content,
                "p_source": source,
                "p_project_id": project_id,
                "p_metadata": metadata or {},
            },
        )
        if not rows:
            raise RuntimeError("cerefox_save_note returned no data")
        return rows[0]

    # ── Audit log ────────────────────────────────────────────────────────────

    def create_audit_entry(
        self,
        operation: str,
        author: str = "unknown",
        author_type: str = "user",
        document_id: str | None = None,
        version_id: str | None = None,
        size_before: int | None = None,
        size_after: int | None = None,
        description: str = "",
    ) -> dict[str, Any]:
        """Insert an immutable audit log entry.

        Args:
            operation: One of 'create', 'update-content', 'update-metadata',
                       'delete', 'status-change', 'archive', 'unarchive'.
            author: Human username, agent name/model, or 'system'.
            author_type: 'user' (human via web UI/CLI) or 'agent' (AI via MCP/Edge Function).
                         Used for review_status auto-transition decisions.
            document_id: UUID of the affected document (nullable).
            version_id: UUID of the version created by this operation (nullable).
            size_before: Document total_chars before the operation.
            size_after: Document total_chars after the operation.
            description: Free-text explaining what changed and why.
        """
        rows = self.rpc(
            "cerefox_create_audit_entry",
            {
                "p_document_id": document_id,
                "p_version_id": version_id,
                "p_operation": operation,
                "p_author": author,
                "p_author_type": author_type,
                "p_size_before": size_before,
                "p_size_after": size_after,
                "p_description": description,
            },
        )
        return rows[0] if rows else {}

    def list_audit_entries(
        self,
        document_id: str | None = None,
        author: str | None = None,
        operation: str | None = None,
        since: str | None = None,
        until: str | None = None,
        limit: int = 50,
    ) -> list[dict[str, Any]]:
        """Query audit log entries with optional filters.

        Args:
            document_id: Filter to entries for a specific document.
            author: Filter by author (exact match).
            operation: Filter by operation type.
            since: ISO timestamp -- return entries created after this time.
            until: ISO timestamp -- return entries created before this time.
            limit: Max entries to return (default 50).
        """
        return self.rpc(
            "cerefox_list_audit_entries",
            {
                "p_document_id": document_id,
                "p_author": author,
                "p_operation": operation,
                "p_since": since,
                "p_until": until,
                "p_limit": limit,
            },
        )

    # ── Review status ────────────────────────────────────────────────────────

    def set_review_status(
        self, document_id: str, status: str, author: str = "unknown"
    ) -> dict[str, Any]:
        """Set the review_status of a document and create an audit entry.

        Args:
            document_id: UUID of the document.
            status: 'approved' or 'pending_review'.
            author: Who is making the change.
        """
        if status not in ("approved", "pending_review"):
            raise ValueError(f"Invalid review_status: {status!r}")
        old = self.get_document_by_id(document_id)
        old_status = old.get("review_status", "unknown") if old else "unknown"
        resp = (
            self.client.table("cerefox_documents")
            .update({"review_status": status, "updated_at": "now()"})
            .eq("id", document_id)
            .execute()
        )
        self.create_audit_entry(
            operation="status-change",
            author=author,
            document_id=document_id,
            description=f"Review status changed from '{old_status}' to '{status}'",
        )
        return resp.data[0] if resp.data else {}

    # ── Version archival ─────────────────────────────────────────────────────

    def set_version_archived(
        self, version_id: str, archived: bool, author: str = "unknown"
    ) -> dict[str, Any]:
        """Set or clear the archived flag on a document version.

        Args:
            version_id: UUID of the version to archive/unarchive.
            archived: True to protect from cleanup, False to unprotect.
            author: Who is making the change.
        """
        resp = (
            self.client.table("cerefox_document_versions")
            .update({"archived": archived})
            .eq("id", version_id)
            .execute()
        )
        ver = resp.data[0] if resp.data else {}
        op = "archive" if archived else "unarchive"
        doc_id = ver.get("document_id")
        ver_num = ver.get("version_number", "?")
        self.create_audit_entry(
            operation=op,
            author=author,
            document_id=doc_id,
            version_id=version_id,
            description=f"Version {ver_num} {'archived (protected from cleanup)' if archived else 'unarchived (eligible for cleanup)'}",
        )
        return ver
