db.py

from sqlalchemy import CheckConstraint
import networkx as nx
import uuid
import neo4j
import os
import logging
import re
import time
import yaml

from pprint import pprint

from collections import Counter, defaultdict
from itertools import permutations
from typing import Any, Dict, List, Optional, Tuple, cast
from neomodel.exceptions import (
    DoesNotExist,
    FeatureNotSupported,
    NodeClassAlreadyDefined,
)
from flask import json as flask_json
from sqlalchemy.orm import aliased
from flask_sqlalchemy.model import DefaultMeta
from sqlalchemy import func, delete, cast as sql_cast, literal, or_
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.exc import OperationalError, IntegrityError

from neomodel import (
    config,
    StructuredNode,
    StringProperty,
    UniqueIdProperty,
    Relationship,
    RelationshipTo,
    RelationshipFrom,
    ArrayProperty,
    StructuredRel,
    db,
)
from application.database import inmemory_graph
from application.utils import redis
from application.defs import cre_defs
from application.utils import file
from application.utils.gap_analysis import (
    gap_analysis_cache_key_is_primary,
    get_path_score,
    make_resources_key,
    make_subresources_key,
    primary_gap_analysis_payload_is_material,
    should_persist_primary_gap_analysis_cache,
)


from .. import sqla  # type: ignore

logging.basicConfig()
logger = logging.getLogger(__name__)
logger.setLevel(logging.INFO)


BaseModel: DefaultMeta = sqla.Model


def generate_uuid():
    return str(uuid.uuid4())


class Node(BaseModel):  # type: ignore
    __tablename__ = "node"
    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)

    # ASVS or standard name,  what are we linking to
    name = sqla.Column(sqla.String)

    # which part of <name> are we linking to
    section = sqla.Column(sqla.String, nullable=True)

    # which subpart of <name> are we linking to
    subsection = sqla.Column(sqla.String)

    # coma separated tags
    tags = sqla.Column(sqla.String)
    version = sqla.Column(sqla.String)
    description = sqla.Column(sqla.String)
    ntype = sqla.Column(sqla.String)

    section_id = sqla.Column(sqla.String, nullable=True)

    # some external link to where this is, usually a URL with an anchor
    link = sqla.Column(sqla.String, default="")

    # arbitrary metadata for this node (JSON)
    # stored in column "document_metadata" to avoid clashing with SQLAlchemy's reserved "metadata" name
    metadata_json = sqla.Column("document_metadata", sqla.JSON, nullable=True)

    __table_args__ = (
        sqla.UniqueConstraint(
            name,
            section,
            subsection,
            version,
            section_id,
            name="uq_node",
        ),
    )


class CRE(BaseModel):  # type: ignore
    __tablename__ = "cre"
    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)

    external_id = sqla.Column(sqla.String, default="")
    description = sqla.Column(sqla.String, default="")
    name = sqla.Column(sqla.String)
    tags = sqla.Column(sqla.String, default="")  # coma separated tags

    # arbitrary metadata for this CRE (JSON)
    # stored in column "document_metadata" to avoid clashing with SQLAlchemy's reserved "metadata" name
    metadata_json = sqla.Column("document_metadata", sqla.JSON, nullable=True)

    __table_args__ = (
        sqla.UniqueConstraint(name, external_id, name="unique_cre_fields"),
    )


class InternalLinks(BaseModel):  # type: ignore
    # model cre-groups linking cres
    __tablename__ = "cre_links"
    type = sqla.Column(sqla.String)

    group = sqla.Column(
        sqla.String,
        sqla.ForeignKey("cre.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    )
    cre = sqla.Column(
        sqla.String,
        sqla.ForeignKey("cre.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    )
    __table_args__ = (
        sqla.UniqueConstraint(
            group,
            cre,
            name="uq_cre_link_pair",
        ),
        sqla.CheckConstraint("type != 'PartOf'", name="No 'PartOf' links"),
    )


class Links(BaseModel):  # type: ignore
    __tablename__ = "cre_node_links"
    type = sqla.Column(sqla.String)
    cre = sqla.Column(
        sqla.String,
        sqla.ForeignKey("cre.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    )
    node = sqla.Column(
        sqla.String,
        sqla.ForeignKey("node.id", onupdate="CASCADE", ondelete="CASCADE"),
        primary_key=True,
    )
    __table_args__ = (
        sqla.UniqueConstraint(
            cre,
            node,
            name="uq_cre_node_link_pair",
        ),
    )


class Embeddings(BaseModel):  # type: ignore
    __tablename__ = "embeddings"

    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)
    embeddings = sqla.Column(sqla.String, nullable=False)
    doc_type = sqla.Column(sqla.String, nullable=False)
    cre_id = sqla.Column(
        sqla.String,
        sqla.ForeignKey("cre.id", onupdate="CASCADE", ondelete="CASCADE"),
        nullable=True,
    )
    node_id = sqla.Column(
        sqla.String,
        sqla.ForeignKey("node.id", onupdate="CASCADE", ondelete="CASCADE"),
        nullable=True,
    )

    embeddings_url = sqla.Column(sqla.String, nullable=True, default=None)
    embeddings_content = sqla.Column(sqla.String, nullable=True, default=None)
    embedding_model_id = sqla.Column(sqla.String, nullable=True, default=None)
    embedding_dim = sqla.Column(sqla.Integer, nullable=True, default=None)


class GapAnalysisResults(BaseModel):
    __tablename__ = "gap_analysis_results"
    cache_key = sqla.Column(sqla.String, primary_key=True)
    ga_object = sqla.Column(sqla.String)
    __table_args__ = (sqla.UniqueConstraint(cache_key, name="unique_cache_key_field"),)


class ImportRun(BaseModel):  # type: ignore
    """Tracks import runs for Module C diff/staging. Step 6."""

    __tablename__ = "import_run"
    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)
    source = sqla.Column(sqla.String, nullable=False)
    version = sqla.Column(sqla.String, nullable=True)
    created_at = sqla.Column(sqla.DateTime, nullable=False)


class StandardSnapshot(BaseModel):  # type: ignore
    """Canonical standard snapshot persisted per import run (Phase 2 Step 1)."""

    __tablename__ = "standard_snapshot"
    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)
    run_id = sqla.Column(
        sqla.String,
        sqla.ForeignKey("import_run.id", onupdate="CASCADE", ondelete="CASCADE"),
        nullable=False,
    )
    standard_name = sqla.Column(sqla.String, nullable=False)
    snapshot_json = sqla.Column(sqla.Text, nullable=False)
    content_hash = sqla.Column(sqla.String, nullable=False)
    created_at = sqla.Column(sqla.DateTime, nullable=False)

    __table_args__ = (
        sqla.UniqueConstraint(
            run_id,
            standard_name,
            name="uq_standard_snapshot_run_standard",
        ),
    )


class StagedChangeSet(BaseModel):  # type: ignore
    """Structured change set persisted per import run (Phase 2 Step 2)."""

    __tablename__ = "staged_change_set"
    id = sqla.Column(sqla.String, primary_key=True, default=generate_uuid)
    run_id = sqla.Column(
        sqla.String,
        sqla.ForeignKey("import_run.id", onupdate="CASCADE", ondelete="CASCADE"),
        nullable=False,
        unique=True,
    )
    changeset_json = sqla.Column(sqla.Text, nullable=False, default="[]")
    has_conflicts = sqla.Column(sqla.Boolean, nullable=False, default=False)
    staging_status = sqla.Column(
        sqla.String, nullable=False, default="pending_review"
    )  # pending_review|accepted|applied|discarded
    apply_error = sqla.Column(sqla.Text, nullable=True)
    created_at = sqla.Column(sqla.DateTime, nullable=False)


def create_import_run(source: str, version: Optional[str] = None) -> ImportRun:
    """Create and persist an import run record. Returns the new ImportRun."""
    from datetime import datetime, timezone

    run = ImportRun(
        id=generate_uuid(),
        source=source,
        version=version,
        created_at=datetime.now(timezone.utc),
    )
    sqla.session.add(run)
    sqla.session.commit()
    return run


def get_latest_import_run(source: str) -> Optional[ImportRun]:
    """Get the most recent import run for a source."""
    return (
        sqla.session.query(ImportRun)
        .filter(ImportRun.source == source)
        .order_by(ImportRun.created_at.desc(), ImportRun.id.desc())
        .first()
    )


def get_previous_import_run(source: str, current_run_id: str) -> Optional[ImportRun]:
    """Get the import run immediately before `current_run_id` for `source`."""
    current = (
        sqla.session.query(ImportRun).filter(ImportRun.id == current_run_id).first()
    )
    if not current:
        return None
    return (
        sqla.session.query(ImportRun)
        .filter(ImportRun.source == source)
        .filter(
            (ImportRun.created_at < current.created_at)
            | (
                (ImportRun.created_at == current.created_at)
                & (ImportRun.id < current.id)
            )
        )
        .order_by(ImportRun.created_at.desc(), ImportRun.id.desc())
        .first()
    )


def persist_standard_snapshot(
    *,
    run_id: str,
    standard_name: str,
    snapshot_json: str,
    content_hash: str,
) -> StandardSnapshot:
    from datetime import datetime, timezone

    snap = StandardSnapshot(
        id=generate_uuid(),
        run_id=run_id,
        standard_name=standard_name,
        snapshot_json=snapshot_json,
        content_hash=content_hash,
        created_at=datetime.now(timezone.utc),
    )
    sqla.session.add(snap)
    sqla.session.commit()
    return snap


def get_standard_snapshot(
    *, run_id: str, standard_name: str
) -> Optional[StandardSnapshot]:
    return (
        sqla.session.query(StandardSnapshot)
        .filter(StandardSnapshot.run_id == run_id)
        .filter(StandardSnapshot.standard_name == standard_name)
        .first()
    )


def persist_staged_change_set(
    *,
    run_id: str,
    changeset_json: str,
    has_conflicts: bool = False,
    staging_status: str = "pending_review",
) -> StagedChangeSet:
    from datetime import datetime, timezone

    cs = StagedChangeSet(
        id=generate_uuid(),
        run_id=run_id,
        changeset_json=changeset_json,
        has_conflicts=has_conflicts,
        staging_status=staging_status,
        created_at=datetime.now(timezone.utc),
    )
    sqla.session.add(cs)
    sqla.session.commit()
    return cs


def get_staged_change_set(*, run_id: str) -> Optional[StagedChangeSet]:
    return (
        sqla.session.query(StagedChangeSet)
        .filter(StagedChangeSet.run_id == run_id)
        .first()
    )


def update_staged_change_set(
    *,
    run_id: str,
    staging_status: Optional[str] = None,
    has_conflicts: Optional[bool] = None,
    apply_error: Optional[str] = None,
) -> Optional[StagedChangeSet]:
    cs = get_staged_change_set(run_id=run_id)
    if not cs:
        return None
    if staging_status is not None:
        cs.staging_status = staging_status
    if has_conflicts is not None:
        cs.has_conflicts = has_conflicts
    if apply_error is not None:
        cs.apply_error = apply_error
    sqla.session.add(cs)
    sqla.session.commit()
    return cs


def get_import_run(*, run_id: str) -> Optional[ImportRun]:
    return sqla.session.query(ImportRun).filter(ImportRun.id == run_id).first()


def list_import_runs(
    *,
    source: Optional[str] = None,
    limit: int = 50,
    offset: int = 0,
) -> List[ImportRun]:
    q = sqla.session.query(ImportRun)
    if source:
        q = q.filter(ImportRun.source == source)
    return (
        q.order_by(ImportRun.created_at.desc(), ImportRun.id.desc())
        .offset(offset)
        .limit(limit)
        .all()
    )


class RelatedRel(StructuredRel):
    pass


class ContainsRel(StructuredRel):
    pass


class AutoLinkedToRel(StructuredRel):
    pass


class LinkedToRel(StructuredRel):
    pass


class SameRel(StructuredRel):
    pass


class NeoDocument(StructuredNode):
    document_id = UniqueIdProperty()
    name = StringProperty(required=True)
    description = StringProperty(required=True)
    tags = ArrayProperty(StringProperty())
    doctype = StringProperty(required=True)
    related = Relationship("NeoDocument", "RELATED", model=RelatedRel)

    @classmethod
    def to_cre_def(self, node, parse_links=True):
        raise Exception(f"Shouldn't be parsing a NeoDocument")

    @classmethod
    def get_links(self, links_dict):
        links = []
        for key in links_dict:
            links.extend(
                [
                    cre_defs.Link(c.to_cre_def(c, parse_links=False), key)
                    for c in links_dict[key]
                ]
            )
        return links


class NeoNode(NeoDocument):
    doctype = StringProperty()
    version = StringProperty(required=True)
    hyperlink = StringProperty()

    @classmethod
    def to_cre_def(self, node, parse_links=True):
        raise Exception(f"Shouldn't be parsing a NeoNode")


class NeoStandard(NeoNode):
    section = StringProperty()
    subsection = StringProperty()
    section_id = StringProperty()

    @classmethod
    def to_cre_def(self, node, parse_links=True) -> cre_defs.Standard:
        return cre_defs.Standard(
            name=node.name,
            description=node.description,
            tags=node.tags,
            hyperlink=node.hyperlink,
            version=node.version,
            section=node.section,
            sectionID=node.section_id,
            subsection=node.subsection,
            links=(
                self.get_links(
                    {
                        "Related": node.related,
                    }
                )
                if parse_links
                else []
            ),
        )


class NeoTool(NeoStandard):
    tooltype = StringProperty(required=True)

    @classmethod
    def to_cre_def(self, node, parse_links=True) -> cre_defs.Tool:
        return cre_defs.Tool(
            tooltype=node.tooltype,
            name=node.name,
            description=node.description,
            tags=node.tags,
            hyperlink=node.hyperlink,
            version=node.version,
            section=node.section,
            sectionID=node.section_id,
            subsection=node.subsection,
            links=(
                self.get_links(
                    {
                        "Related": node.related,
                    }
                )
                if parse_links
                else []
            ),
        )


class NeoCode(NeoNode):
    @classmethod
    def to_cre_def(self, node, parse_links=True) -> cre_defs.Code:
        return cre_defs.Code(
            name=node.name,
            description=node.description,
            tags=node.tags,
            hyperlink=node.hyperlink,
            version=node.version,
            links=(
                self.get_links(
                    {
                        "Related": node.related,
                    }
                )
                if parse_links
                else []
            ),
        )


class NeoCRE(NeoDocument):  # type: ignore
    external_id = StringProperty()
    contains = RelationshipTo("NeoCRE", "CONTAINS", model=ContainsRel)
    contained_in = RelationshipFrom("NeoCRE", "CONTAINS", model=ContainsRel)
    linked = RelationshipTo("NeoStandard", "LINKED_TO", model=LinkedToRel)
    auto_linked_to = RelationshipTo(
        "NeoStandard", "AUTOMATICALLY_LINKED_TO", model=AutoLinkedToRel
    )

    @classmethod
    def to_cre_def(self, node, parse_links=True) -> cre_defs.CRE:
        return cre_defs.CRE(
            name=node.name,
            id=node.external_id,
            description=node.description,
            tags=node.tags,
            links=(
                self.get_links(
                    {
                        "Contains": [*node.contains, *node.contained_in],
                        "Linked To": node.linked,
                        "Same as": node.same_as,
                        "Related": node.related,
                        "Automatically linked to": node.auto_linked_to,
                    }
                )
                if parse_links
                else []
            ),
        )


def _neo_node_first_or_none_by_document_id(document_id: str) -> Any:
    """
    Find a Standard/Code/Tool graph node by SQL ``document_id`` without using
    ``MATCH (:NeoNode)``.

    Neo4j 5+ emits ``UnknownLabelWarning`` when a query pattern references a
    label that is not yet in the catalog (e.g. empty graph at first import).
    Using ``'NeoNode' IN labels(n)`` after ``MATCH (n)`` avoids that: the
    planner does not require the label to exist in the catalog.
    """
    results, _ = db.cypher_query(
        """
        MATCH (n)
        WHERE n.document_id = $doc_id AND 'NeoNode' IN labels(n)
        RETURN n
        LIMIT 1
        """,
        {"doc_id": document_id},
        resolve_objects=True,
    )
    if not results:
        return None
    return results[0][0]


class NEO_DB:
    __instance = None

    driver = None
    connected = False

    @classmethod
    def instance(self):
        if self.__instance is None:
            self.__instance = self.__new__(self)

            config.DATABASE_URL = (
                os.getenv("NEO4J_URL") or "bolt://neo4j:password@localhost:7687"
            )
        return self.__instance

    def __init__(sel):
        raise ValueError("NEO_DB is a singleton, please call instance() instead")

    @classmethod
    def populate_DB(self, session):
        for il in session.query(InternalLinks).all():
            group = session.query(CRE).filter(CRE.id == il.group).first()
            if not group:
                logger.error(f"CRE {il.group} does not exist?")
            self.add_cre(group)

            cre = session.query(CRE).filter(CRE.id == il.cre).first()
            if not cre:
                logger.error(f"CRE {il.cre} does not exist?")
            self.add_cre(cre)

            self.link_CRE_to_CRE(il.group, il.cre, il.type)

        for lnk in session.query(Links).all():
            node = session.query(Node).filter(Node.id == lnk.node).first()
            if not node:
                logger.error(f"Node {lnk.node} does not exist?")
                continue
            self.add_dbnode(node)

            cre = session.query(CRE).filter(CRE.id == lnk.cre).first()
            self.add_cre(cre)

            self.link_CRE_to_Node(lnk.cre, lnk.node, lnk.type)

    @classmethod
    def add_cre(self, dbcre: CRE):
        document = NeoCRE.nodes.first_or_none(document_id=dbcre.id)
        if not document:
            return NeoCRE(
                name=dbcre.name,
                doctype=cre_defs.Credoctypes.CRE.value,  # dbcre.ntype,
                document_id=dbcre.id,
                description=dbcre.description,
                links=[],  # dbcre.links,
                tags=[dbcre.tags] if isinstance(dbcre.tags, str) else dbcre.tags,
                external_id=dbcre.external_id,
            ).save()

        document.name = dbcre.name
        document.doctype = (cre_defs.Credoctypes.CRE.value,)
        document.document_id = dbcre.id
        document.description = dbcre.description or ""
        document.tags = [dbcre.tags] if isinstance(dbcre.tags, str) else dbcre.tags
        document.metadata = {}
        document.external_id = dbcre.external_id or ""
        return document.save()

    def __create_dbnode(dbnode: Node):
        if dbnode.ntype == "Standard":
            return NeoStandard(
                name=dbnode.name,
                doctype=dbnode.ntype,
                document_id=dbnode.id,
                description=dbnode.description or "",
                tags=[dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags,
                metadata={},
                version=dbnode.version or "",
                section=dbnode.section,
                section_id=dbnode.section_id,  # dbnode.sectionID,
                subsection=dbnode.subsection or "",
                tooltype="",  # dbnode.tooltype,
            ).save()
        elif dbnode.ntype == "Code":
            return NeoCode(
                name=dbnode.name,
                doctype=dbnode.ntype,
                document_id=dbnode.id,
                description=dbnode.description,
                links=[],  # dbnode.links,
                tags=([dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags),
                metadata="{}",  # dbnode.metadata,
                hyperlink="",  # dbnode.hyperlink or "",
                version=dbnode.version or "",
            ).save()
        elif dbnode.ntype == "Tool":
            ttype = [tag for tag in dbnode.tags if tag in cre_defs.ToolTypes]
            if ttype:
                ttype = ttype[0]
            else:
                ttype = cre_defs.ToolTypes.Unknown
            return NeoTool(
                tooltype=ttype,
                name=dbnode.name,
                doctype=dbnode.ntype,
                document_id=dbnode.id,
                description=dbnode.description,
                links=[],  # dbnode.links,
                tags=([dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags),
                metadata="{}",  # dbnode.metadata,
                hyperlink="",  # dbnode.hyperlink or "",
                version=dbnode.version or "",
            ).save()

    def __update_dbnode(dbnode: Node):
        existing = _neo_node_first_or_none_by_document_id(dbnode.id)
        if dbnode.ntype == "Standard":
            existing.name = dbnode.name
            existing.doctype = dbnode.ntype
            existing.document_id = dbnode.id
            existing.description = dbnode.description or ""
            existing.tags = (
                [dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags
            )
            existing.metadata = {}
            existing.version = dbnode.version or ""
            existing.section = dbnode.section
            existing.section_id = dbnode.section_id  # dbnode.sectionID
            existing.subsection = dbnode.subsection or ""
            existing.tooltype = ""  # dbnode.tooltype
            return existing.save()
        elif dbnode.ntype == "Code":
            existing.name = dbnode.name
            existing.doctype = dbnode.ntype
            existing.document_id = dbnode.id
            existing.description = dbnode.description
            existing.links = []  # dbnode.links
            existing.tags = (
                [dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags
            )
            existing.metadata = "{}"  # dbnode.metadata,
            existing.hyperlink = ""  # dbnode.hyperlink or "",
            existing.version = dbnode.version or ""
            return existing.save()
        elif dbnode.ntype == "Tool":
            existing.name = dbnode.name
            existing.doctype = dbnode.ntype
            existing.document_id = dbnode.id
            existing.description = dbnode.description
            existing.links = []  # dbnode.links
            existing.tags = (
                [dbnode.tags] if isinstance(dbnode.tags, str) else dbnode.tags
            )
            ttype = [tag for tag in dbnode.tags if tag in cre_defs.ToolTypes]
            if ttype:
                ttype = ttype[0]
            else:
                ttype = cre_defs.ToolTypes.Unknown
            existing.tooltype = ttype
            existing.metadata = "{}"  # dbnode.metadata,
            existing.hyperlink = ""  # dbnode.hyperlink or "",
            existing.version = dbnode.version or ""
            return existing.save()
        else:
            raise Exception(f"Unknown DB type: {dbnode.ntype}")

    @classmethod
    @db.transaction
    def add_dbnode(self, dbnode: Node):
        document = _neo_node_first_or_none_by_document_id(dbnode.id)
        if document:
            return self.__update_dbnode(dbnode)
        return self.__create_dbnode(dbnode)

    @classmethod
    def link_CRE_to_CRE(self, id1, id2, link_type):
        cre1 = NeoCRE.nodes.get(document_id=id1)
        cre2 = NeoCRE.nodes.get(document_id=id2)

        if link_type == cre_defs.LinkTypes.Contains.value:
            cre1.contains.connect(cre2)
            return
        if link_type == cre_defs.LinkTypes.Related.value:
            cre1.related.connect(cre2)
            return
        if link_type == cre_defs.LinkTypes.PartOf.value:
            cre2.contains.connect(cre1)
            return
        raise Exception(f"Unknown relation type {link_type}")

    @classmethod
    def link_CRE_to_Node(self, CRE_id, node_id, link_type):
        cre = NeoCRE.nodes.first_or_none(document_id=CRE_id)
        node = _neo_node_first_or_none_by_document_id(node_id)
        if not node:
            return
        if link_type == cre_defs.LinkTypes.AutomaticallyLinkedTo.value:
            cre.auto_linked_to.connect(node)
            return
        elif link_type == cre_defs.LinkTypes.LinkedTo.value:
            cre.linked.connect(node)
            return
        raise Exception(f"Unknown relation type {link_type} for Nodes to CREs")

    @classmethod
    def gap_analysis(self, name_1, name_2):
        logger.info(f"Performing GraphDB queries for gap analysis {name_1}>>{name_2}")
        base_standard = NeoStandard.nodes.filter(name=name_1)
        denylist = ["Cross-cutting concerns"]
        from datetime import datetime

        # Tier 1: Strong Links (LINKED_TO, SAME, AUTOMATICALLY_LINKED_TO)
        path_records, _ = db.cypher_query(
            """
         MATCH (BaseStandard:NeoStandard {name: $name1})
         MATCH (CompareStandard:NeoStandard {name: $name2})
         MATCH p = allShortestPaths((BaseStandard)-[:(LINKED_TO|AUTOMATICALLY_LINKED_TO|SAME)*..20]-(CompareStandard))
         WITH p
         WHERE length(p) > 1 AND ALL(n in NODES(p) WHERE (n:NeoCRE or n = BaseStandard or n = CompareStandard) AND NOT n.name in $denylist)
         RETURN p
            """,
            {"name1": name_1, "name2": name_2, "denylist": denylist},
            resolve_objects=True,
        )

        # If strict strong links found, return early (Pruning)
        if path_records and len(path_records) > 0:
            logger.info(
                f"Gap Analysis: Tier 1 (Strong) found {len(path_records)} paths. Pruning remainder."
            )
            # Helper to format and return
            return self._format_gap_analysis_response(base_standard, path_records)

        # Tier 2: Medium Links (Add CONTAINS to the mix)
        path_records, _ = db.cypher_query(
            """
         MATCH (BaseStandard:NeoStandard {name: $name1})
         MATCH (CompareStandard:NeoStandard {name: $name2})
         MATCH p = allShortestPaths((BaseStandard)-[:(LINKED_TO|AUTOMATICALLY_LINKED_TO|SAME|CONTAINS)*..20]-(CompareStandard))
         WITH p
         WHERE length(p) > 1 AND ALL(n in NODES(p) WHERE (n:NeoCRE or n = BaseStandard or n = CompareStandard) AND NOT n.name in $denylist)
         RETURN p
            """,
            {"name1": name_1, "name2": name_2, "denylist": denylist},
            resolve_objects=True,
        )

        if path_records and len(path_records) > 0:
            logger.info(
                f"Gap Analysis: Tier 2 (Medium) found {len(path_records)} paths. Pruning remainder."
            )
            return self._format_gap_analysis_response(base_standard, path_records)

        # Tier 3: Weak/All Links (Wildcard - The original expensive query)
        logger.info(
            "Gap Analysis: Tiers 1 & 2 empty. Executing Tier 3 (Wildcard search)."
        )
        path_records_all, _ = db.cypher_query(
            """
         MATCH (BaseStandard:NeoStandard {name: $name1})
         MATCH (CompareStandard:NeoStandard {name: $name2})
         MATCH p = allShortestPaths((BaseStandard)-[*..20]-(CompareStandard))
         WITH p
         WHERE length(p) > 1 AND ALL (n in NODES(p) where (n:NeoCRE or n = BaseStandard or n = CompareStandard) AND NOT n.name in $denylist) 
         RETURN p
            """,
            {"name1": name_1, "name2": name_2, "denylist": denylist},
            resolve_objects=True,
        )

        return self._format_gap_analysis_response(base_standard, path_records_all)

    @classmethod
    def _format_gap_analysis_response(self, base_standard, path_records):
        def safe_parse_node_no_links(node):
            try:
                return NEO_DB.parse_node_no_links(node)
            except Exception as e:
                logger.warning(
                    "Skipping malformed Neo4j node while formatting gap analysis response: %s",
                    e,
                )
                return None

        def format_segment(seg: StructuredRel, nodes):
            relation_map = {
                RelatedRel: "RELATED",
                ContainsRel: "CONTAINS",
                LinkedToRel: "LINKED_TO",
                AutoLinkedToRel: "AUTOMATICALLY_LINKED_TO",
                SameRel: "SAME",
            }
            start_node = [
                node for node in nodes if node.element_id == seg._start_node_element_id
            ][0]
            end_node = [
                node for node in nodes if node.element_id == seg._end_node_element_id
            ][0]

            # Default to RELATED if relation unknown (though mostly governed by class type)
            rtype = relation_map.get(type(seg), "RELATED")

            parsed_start = safe_parse_node_no_links(start_node)
            parsed_end = safe_parse_node_no_links(end_node)
            if parsed_start is None or parsed_end is None:
                return None

            return {
                "start": parsed_start,
                "end": parsed_end,
                "relationship": rtype,
            }

        def format_path_record(rec):
            parsed_start = safe_parse_node_no_links(rec.start_node)
            parsed_end = safe_parse_node_no_links(rec.end_node)
            if parsed_start is None or parsed_end is None:
                return None
            parsed_segments = []
            for seg in rec.relationships:
                segment = format_segment(seg, rec.nodes)
                if segment is None:
                    # Skip malformed segment; keep valid rest of path.
                    continue
                parsed_segments.append(segment)
            return {
                "start": parsed_start,
                "end": parsed_end,
                "path": parsed_segments,
            }

        parsed_base = []
        for rec in base_standard:
            parsed = safe_parse_node_no_links(rec)
            if parsed is not None:
                parsed_base.append(parsed)

        parsed_paths = []
        for rec in path_records:
            parsed = format_path_record(rec[0])
            if parsed is not None:
                parsed_paths.append(parsed)

        return parsed_base, parsed_paths

    @classmethod
    def standards(self) -> List[str]:
        results = []
        for x in db.cypher_query("""MATCH (n:NeoTool) RETURN DISTINCT n.name""")[0]:
            results.extend(x)
        for x in db.cypher_query("""MATCH (n:NeoStandard) RETURN DISTINCT n.name""")[0]:
            results.extend(x)
        return list(set(results))

    @classmethod
    def everything(self) -> List[str]:
        try:
            return [NEO_DB.parse_node(rec) for rec in NeoDocument.nodes.all()]
        except neo4j.exceptions.ServiceUnavailable:
            logger.error("Neo4j DB offline")
            return None

    @staticmethod
    def parse_node(node: NeoDocument) -> cre_defs.Document:
        return node.to_cre_def(node)

    @staticmethod
    def parse_node_no_links(node: NeoDocument) -> cre_defs.Document:
        return node.to_cre_def(node, parse_links=False)


class Node_collection:
    graph: inmemory_graph.CRE_Graph = None
    neo_db: NEO_DB = None
    session = sqla.session

    def __init__(self) -> None:
        if os.environ.get("NO_LOAD_GRAPH_DB") != "1":
            self.neo_db = NEO_DB.instance()
        self.session = sqla.session

    def with_graph(self) -> "Node_collection":
        if self.graph is not None:
            logger.debug("CRE graph already loaded, skipping reload")
            return self

        logger.info("Loading CRE graph in memory, memory-heavy operation!")
        self.graph = inmemory_graph.CRE_Graph()
        graph_singleton = inmemory_graph.Singleton_Graph_Storage.instance()
        self.graph.with_graph(
            graph=graph_singleton,
            graph_data=self.__get_all_nodes_and_cres(cres_only=True),
        )
        logger.info("Successfully loaded CRE graph in memory")

        return self

    def __get_external_links(self) -> List[Tuple[CRE, Node, str]]:
        external_links: List[Tuple[CRE, Node, str]] = []

        all_links = self.session.query(Links).all()
        for link in all_links:
            cre = self.session.query(CRE).filter(CRE.id == link.cre).first()
            node: Node = self.session.query(Node).filter(Node.id == link.node).first()
            external_links.append((cre, node, link.type))
        return external_links

    def __get_internal_links(self) -> List[Tuple[CRE, CRE, str]]:
        internal_links = []
        all_internal_links = self.session.query(InternalLinks).all()
        for il in all_internal_links:
            group = self.session.query(CRE).filter(CRE.id == il.group).first()
            cre = self.session.query(CRE).filter(CRE.id == il.cre).first()
            internal_links.append((group, cre, il.type))
        return internal_links

    def __get_unlinked_nodes(self) -> List[Node]:
        linked_nodes = (
            self.session.query(Node.id).join(Links).filter(Node.id == Links.node)
        )
        nodes: List[Node] = (
            self.session.query(Node).filter(Node.id.notin_(linked_nodes)).all()
        )
        return nodes

    def __get_unlinked_cres(self) -> List[CRE]:
        internally_linked_cres = self.session.query(CRE.id).join(
            InternalLinks,
            sqla.or_(InternalLinks.group == CRE.id, InternalLinks.cre == CRE.id),
        )
        externally_linked_cres = (
            self.session.query(CRE.id).join(Links).filter(Links.cre == CRE.id)
        )

        cres = (
            self.session.query(CRE)
            .filter(
                CRE.id.notin_(internally_linked_cres),
                CRE.id.notin_(externally_linked_cres),
            )
            .all()
        )
        return cres

    def __get_all_nodes_and_cres(
        self, cres_only: bool = False
    ) -> List[cre_defs.Document]:
        result = []
        nodes = []
        cres = []
        if not cres_only:
            node_ids = self.session.query(Node.id).all()
            for nid in node_ids:
                result.extend(self.get_nodes(db_id=nid[0]))

        cre_ids = self.session.query(CRE.id).all()
        for cid in cre_ids:
            result.append(self.get_cre_by_db_id(cid[0]))
        return result

    @classmethod
    def object_select(cls, node: Node, skip_attributes: List = []) -> List[Node]:
        if not node:
            return []
        qu = Node.query.filter()

        for vk, v in vars(node).items():
            if vk not in skip_attributes and hasattr(Node, vk):
                # Treat identity-like empty strings as real values (do not use
                # truthiness). If v is None, skip it.
                if v is None:
                    continue

                attr = getattr(Node, vk)
                if isinstance(v, str):
                    # Treat NULL and "" as the same for stable identity matching.
                    qu = qu.filter(func.coalesce(attr, "") == v)
                elif isinstance(v, (dict, list)):
                    # PostgreSQL has no `json = json` operator; compare as jsonb.
                    bind = sqla.session.get_bind()
                    if bind is not None and bind.dialect.name == "postgresql":
                        qu = qu.filter(
                            sql_cast(attr, JSONB)
                            == sql_cast(literal(v, type_=sqla.JSON), JSONB)
                        )
                    else:
                        qu = qu.filter(attr == v)
                else:
                    qu = qu.filter(attr == v)
            else:
                logger.debug(f"{vk} not in Node")
        return qu.all()

    def get_node_names(
        self, ntype: str = cre_defs.Standard.__name__
    ) -> List[Tuple[str, str]]:
        q = self.session.query(Node.ntype, Node.name).distinct().all()
        if q:
            return [i for i in q]
        return []

    def get_max_internal_connections(self) -> int:
        q = self.session.query(InternalLinks).all()
        grp_count = Counter([x.group for x in q]) or {0: 0}
        cre_count = Counter([x.cre for x in q]) or {0: 0}
        return max([max(cre_count.values()), max(grp_count.values())])

    def find_cres_of_cre(self, cre: CRE) -> Optional[List[CRE]]:
        """returns the higher level CREs of the cre or none
        if no higher level cres link to it"""
        cre_id = self.session.query(CRE.id).filter(CRE.name == cre.name).first()
        links = (
            self.session.query(InternalLinks).filter(InternalLinks.cre == cre_id).all()
        )
        if links:
            result = []
            for link in links:
                result.append(
                    self.session.query(CRE).filter(CRE.id == link.group).first()
                )
            return result

        return None

    def find_cres_of_node(self, node: cre_defs.Node) -> Optional[List[CRE]]:
        """returns the CREs that link to this node or none
        if none link to it"""
        if not node.id:
            if "subsection" not in vars(node):
                node.subsection = ""
            if "section" not in vars(node):
                node.section = ""
            if "version" not in vars(node):
                node.version = ""
            if "sectionID" not in vars(node):
                node.sectionID = ""
            node = (
                self.session.query(Node)
                .filter(
                    sqla.and_(
                        Node.name == node.name,
                        Node.section == node.section,
                        Node.subsection == node.subsection,
                        Node.version == node.version,
                        Node.ntype == type(node).__name__,
                        Node.section_id == node.sectionID,
                    )
                )
                .first()
            )
        if not node:
            return None

        result: List[CRE] = []
        for link in self.session.query(Links).filter(Links.node == node.id).all():
            result.append(self.session.query(CRE).filter(CRE.id == link.cre).first())
        return result or None

    def get_by_tags(self, tags: List[str]) -> List[cre_defs.Document]:
        """Returns the cre_defs.Documents and their Links
        that are tagged with ALL of the tags provided
        """
        # TODO: (spyros), when we have useful tags this needs to be refactored
        #  so both standards and CREs become the same query
        #  and it gets paginated
        nodes_where_clause = []
        cre_where_clause = []
        documents = []

        if not tags:
            return []

        for tag in tags:
            nodes_where_clause.append(sqla.and_(Node.tags.like("%{}%".format(tag))))
            cre_where_clause.append(sqla.and_(CRE.tags.like("%{}%".format(tag))))

        nodes = Node.query.filter(*nodes_where_clause).all() or []
        for node in nodes:
            node = self.get_nodes(
                name=node.name,
                section=node.section,
                subsection=node.subsection,
                version=node.version,
                link=node.link,
                ntype=node.ntype,
                sectionID=node.section_id,
            )
            if node:
                documents.extend(node)
            else:
                logger.fatal(
                    "db.get_node returned None for"
                    "Node %s:%s:%s that exists, BUG!"
                    % (node.name, node.section, node.section_id)
                )

        cres = CRE.query.filter(*cre_where_clause).all() or []
        for c in cres:
            cre = self.get_CREs(external_id=c.external_id, name=c.name)[0]
            if cre:
                documents.append(cre)
            else:
                logger.fatal(
                    "db.get_CRE returned None for CRE %s:%s that exists, BUG!"
                    % (c.id, c.name)
                )
        return documents

    def get_nodes_with_pagination(
        self,
        name: str,
        section: Optional[str] = None,
        subsection: Optional[str] = None,
        link: Optional[str] = None,
        version: Optional[str] = None,
        partial: Optional[bool] = False,
        page: int = 0,
        items_per_page: Optional[int] = None,
        include_only: Optional[List[str]] = None,
        ntype: str = cre_defs.Standard.__name__,
        description: Optional[str] = None,
        sectionID: Optional[str] = None,
    ) -> Tuple[Optional[int], Optional[List[cre_defs.Standard]], Optional[List[Node]]]:
        """
        Returns the relevant node entries of a singular ntype (or ntype irrelevant if ntype==None) and their linked CREs
        include_only: If set, only the CRE ids in the list provided will be returned
        If a standard entry is not linked to by a CRE in the list the Standard entry will be returned empty.
        """
        nodes = []

        dbnodes = self.__get_nodes_query__(
            name=name,
            section=section,
            subsection=subsection,
            link=link,
            version=version,
            partial=partial,
            ntype=ntype,
            description=description,
            sectionID=sectionID,
        ).paginate(page=int(page), per_page=items_per_page, error_out=False)
        total_pages = dbnodes.pages
        if dbnodes.items:
            for dbnode in dbnodes.items:
                node = nodeFromDB(dbnode=dbnode)
                linked_cres = Links.query.filter(Links.node == dbnode.id).all()
                for dbcre_link in linked_cres:
                    dbcre = CRE.query.filter(CRE.id == dbcre_link.cre).first()
                    if dbcre:
                        if not include_only or (
                            include_only
                            and (
                                dbcre.external_id in include_only
                                or dbcre.name in include_only
                            )
                        ):
                            node.add_link(
                                cre_defs.Link(
                                    ltype=cre_defs.LinkTypes.from_str(dbcre_link.type),
                                    document=CREfromDB(dbcre),
                                )
                            )
                nodes.append(node)

            return total_pages, nodes, dbnodes
        else:
            logger.warning(f"Node {name} of type {ntype} does not exist in the db")
            return None, None, None

    # TODO(spyros): merge with above and make "paginate" a boolean switch
    def get_nodes(
        self,
        name: Optional[str] = None,
        section: Optional[str] = None,
        subsection: Optional[str] = None,
        link: Optional[str] = None,
        version: Optional[str] = None,
        partial: Optional[bool] = False,
        include_only: Optional[List[str]] = None,
        description: Optional[str] = None,
        ntype: str = cre_defs.Standard.__name__,
        sectionID: Optional[str] = None,
        db_id: Optional[str] = None,
    ) -> Optional[List[cre_defs.Node]]:
        nodes = []
        nodes_query = None
        if db_id:
            nodes_query = self.session.query(Node).filter(Node.id == db_id)
        else:
            nodes_query = self.__get_nodes_query__(
                name=name,
                section=section,
                subsection=subsection,
                link=link,
                version=version,
                partial=partial,
                ntype=ntype,
                description=description,
                sectionID=sectionID,
            )
        dbnodes = nodes_query.all()
        if dbnodes:
            for dbnode in dbnodes:
                node = nodeFromDB(dbnode=dbnode)
                linked_cres = Links.query.filter(Links.node == dbnode.id).all()
                for dbcre_link in linked_cres:
                    dbcre = CRE.query.filter(CRE.id == dbcre_link.cre).first()
                    if not dbcre:
                        logger.fatal(
                            f"CRE {dbcre_link.cre} exists in the links but not in the cre table, database corrupt?"
                        )
                        raise AssertionError(
                            f"CRE {dbcre_link.cre} exists in the links but not in the cre table, database corrupt?"
                        )
                    if not include_only or (
                        include_only
                        and (
                            dbcre.external_id in include_only
                            or dbcre.name in include_only
                        )
                    ):
                        node.add_link(
                            cre_defs.Link(
                                ltype=cre_defs.LinkTypes.from_str(dbcre_link.type),
                                document=CREfromDB(dbcre),
                            )
                        )
                nodes.append(node)
            return nodes
        else:
            logger.warning(
                f"Node {name} of type {ntype} and section {section} and section_id {sectionID} does not exist in the db"
            )

            return []

    def get_cre_by_db_id(self, id: str) -> cre_defs.CRE:
        """internal method, returns a shallow cre (no links) by its database id

        Args:
            id (str): the uuid of the cre

        Returns:
            cre_defs.CRE: _description_
        """
        external_id = self.session.query(CRE.external_id).filter(CRE.id == id).first()
        if not external_id:
            logger.error(f"CRE {id} does not exist in the db")
            return None
        cres = self.get_CREs(external_id=external_id[0])
        if not cres:
            logger.error(
                f"CRE {id} exists but get_CREs returned no results for external_id={external_id[0]}"
            )
            return None
        return cres[0]

    def list_node_ids_by_ntype(self, ntype: str) -> List[str]:
        # Always return plain strings (never SQLAlchemy row tuples).
        rows = self.session.query(Node.id).filter(Node.ntype == ntype).all()
        return [r[0] for r in rows]

    def list_node_ids_by_name(self, name: str) -> List[str]:
        # Always return plain strings (never SQLAlchemy row tuples).
        rows = self.session.query(Node.id).filter(Node.name == name).all()
        return [r[0] for r in rows]

    def list_cre_ids(self) -> List[str]:
        # Always return plain strings (never SQLAlchemy row tuples).
        rows = self.session.query(CRE.id).all()
        return [r[0] for r in rows]

    def has_node_with_db_id(self, db_id: str) -> bool:
        """True if ``db_id`` is a primary key in ``node`` (any ``ntype``)."""
        return self.session.query(Node.id).filter(Node.id == db_id).first() is not None

    def __get_nodes_query__(
        self,
        name: Optional[str] = None,
        section: Optional[str] = None,
        subsection: Optional[str] = None,
        link: Optional[str] = None,
        version: Optional[str] = None,
        partial: Optional[bool] = False,
        ntype: Optional[str] = None,
        description: Optional[str] = None,
        sectionID: Optional[str] = None,
    ) -> sqla.Query:
        if (
            not name
            and not section
            and not subsection
            and not link
            and not version
            and not description
            and not sectionID
        ):
            raise ValueError("tried to retrieve node with no values")
        query = Node.query
        if name:
            if not partial:
                query = Node.query.filter(func.lower(Node.name) == name.lower())
            else:
                query = Node.query.filter(func.lower(Node.name).like(name.lower()))
        if section:
            if not partial:
                query = query.filter(func.lower(Node.section) == section.lower())
            else:
                query = query.filter(func.lower(Node.section).like(section.lower()))
        if subsection:
            if not partial:
                query = query.filter(func.lower(Node.subsection) == subsection.lower())
            else:
                query = query.filter(
                    func.lower(Node.subsection).like(subsection.lower())
                )
        if link:
            if not partial:
                query = query.filter(Node.link == link)
            else:
                query = query.filter(Node.link.like(link))
        if version:
            if not partial:
                query = query.filter(Node.version == version)
            else:
                query = query.filter(Node.version.like(version))
        if ntype:
            if not partial:
                query = query.filter(Node.ntype == ntype)
            else:
                query = query.filter(Node.ntype.like(ntype))
        if description:
            if not partial:
                query = query.filter(Node.description == description)
            else:
                query = query.filter(Node.description.like(description))
        if sectionID:
            if not partial:
                query = query.filter(func.lower(Node.section_id) == sectionID.lower())
            else:
                query = query.filter(
                    func.lower(Node.section_id).like(sectionID.lower())
                )
        return query

    def get_CREs(
        self,
        external_id: Optional[str] = None,
        name: Optional[str] = None,
        description: Optional[str] = None,
        partial: Optional[bool] = False,
        include_only: Optional[List[str]] = None,
        internal_id: Optional[str] = None,
    ) -> List[cre_defs.CRE]:
        query = CRE.query
        if not external_id and not name and not description and not internal_id:
            logger.error(
                "You need to search by external_id, internal_id name or description"
            )
            return []

        if external_id:
            if not partial:
                query = query.filter(CRE.external_id == external_id)
            else:
                query = query.filter(CRE.external_id.like(external_id))
        if name:
            if not partial:
                query = query.filter(func.lower(CRE.name) == name.lower())
            else:
                query = query.filter(func.lower(CRE.name).like(name.lower()))
        if description:
            if not partial:
                query = query.filter(func.lower(CRE.description) == description.lower())
            else:
                query = query.filter(
                    func.lower(CRE.description).like(description.lower())
                )
        if internal_id:
            query = CRE.query.filter(CRE.id == internal_id)

        dbcres = query.all()
        if not dbcres:
            logger.warning(
                f"CRE {external_id}:{name}:{description} does not exist in the db"
            )
            return []

        return self._hydrate_cres_batch(dbcres, include_only_nodes=include_only)

    def _hydrate_cres_batch(
        self,
        dbcres: List[CRE],
        include_only_nodes: Optional[List[str]] = None,
    ) -> List[cre_defs.CRE]:
        if not dbcres:
            return []

        cre_ids = [cre.id for cre in dbcres]
        node_links_by_cre: Dict[str, List[Links]] = defaultdict(list)
        node_ids: set = set()
        for link in self.session.query(Links).filter(Links.cre.in_(cre_ids)).all():
            node_links_by_cre[link.cre].append(link)
            node_ids.add(link.node)

        nodes_by_id: Dict[str, Node] = {}
        if node_ids:
            nodes_by_id = {
                node.id: node
                for node in self.session.query(Node).filter(Node.id.in_(node_ids)).all()
            }

        internal_links_by_cre: Dict[str, List[InternalLinks]] = defaultdict(list)
        linked_cre_ids: set = set()
        for internal_link in (
            self.session.query(InternalLinks)
            .filter(
                sqla.or_(
                    InternalLinks.cre.in_(cre_ids),
                    InternalLinks.group.in_(cre_ids),
                )
            )
            .all()
        ):
            linked_cre_ids.add(internal_link.cre)
            linked_cre_ids.add(internal_link.group)
            if internal_link.cre in cre_ids:
                internal_links_by_cre[internal_link.cre].append(internal_link)
            if internal_link.group in cre_ids:
                internal_links_by_cre[internal_link.group].append(internal_link)

        cres_by_id: Dict[str, CRE] = {cre.id: cre for cre in dbcres}
        extra_cre_ids = linked_cre_ids - set(cre_ids)
        if extra_cre_ids:
            for linked in (
                self.session.query(CRE).filter(CRE.id.in_(extra_cre_ids)).all()
            ):
                cres_by_id[linked.id] = linked

        result: List[cre_defs.CRE] = []
        for matching_cre in dbcres:
            cre = CREfromDB(matching_cre)
            cre.links = self._assemble_cre_node_links(
                node_links_by_cre.get(matching_cre.id, []),
                nodes_by_id,
                include_only_nodes,
            )
            seen_internal: set = set()
            internal_rows = []
            for row in internal_links_by_cre.get(matching_cre.id, []):
                key = (row.cre, row.group, row.type)
                if key not in seen_internal:
                    seen_internal.add(key)
                    internal_rows.append(row)
            cre.links.extend(
                self._assemble_cre_internal_links(
                    matching_cre, internal_rows, cres_by_id
                )
            )
            result.append(cre)
        return result

    def _assemble_cre_internal_links(
        self,
        cre: CRE,
        internal_links: List[InternalLinks],
        cres_by_id: Dict[str, CRE],
    ) -> List[cre_defs.Link]:
        links: List[cre_defs.Link] = []
        if not internal_links:
            logger.debug(
                f"CRE {cre.name}:{cre.external_id}:{cre.id} has no internal links"
            )

        for internal_link in internal_links:
            link_type = cre_defs.LinkTypes.from_str(internal_link.type)

            if internal_link.cre == cre.id:
                linked_cre = cres_by_id.get(internal_link.group)
                if not linked_cre:
                    continue
                if link_type == cre_defs.LinkTypes.Contains:
                    links.append(
                        cre_defs.Link(
                            ltype=cre_defs.LinkTypes.PartOf,
                            document=CREfromDB(linked_cre),
                        )
                    )
                elif link_type == cre_defs.LinkTypes.Related:
                    links.append(
                        cre_defs.Link(ltype=link_type, document=CREfromDB(linked_cre))
                    )
                continue

            linked_cre = cres_by_id.get(internal_link.cre)
            if linked_cre:
                links.append(
                    cre_defs.Link(ltype=link_type, document=CREfromDB(linked_cre))
                )
        return links

    def _assemble_cre_node_links(
        self,
        node_links: List[Links],
        nodes_by_id: Dict[str, Node],
        include_only_nodes: Optional[List[str]],
    ) -> List[cre_defs.Link]:
        links: List[cre_defs.Link] = []
        for link in node_links:
            node = nodes_by_id.get(link.node)
            if node and (not include_only_nodes or node.name in include_only_nodes):
                links.append(
                    cre_defs.Link(
                        ltype=cre_defs.LinkTypes.from_str(link.type),
                        document=nodeFromDB(node),
                    )
                )
        return links

    def export(self, dir: str = None, dry_run: bool = False) -> List[cre_defs.Document]:
        """Exports the database to a CRE file collection on disk"""
        docs: Dict[str, cre_defs.Document] = {}
        cre, standard = None, None

        # internal links are Group/HigherLevelCRE -> CRE
        for group, cre, type in self.__get_internal_links():
            grp = None
            # when cres link to each other it's a two way link
            # so handle cre1(group) -> cre2 link first
            if group.name in docs.keys():
                grp = docs[group.name]
            else:
                grp = CREfromDB(group)
            grp.add_link(
                cre_defs.Link(
                    ltype=cre_defs.LinkTypes.from_str(type), document=CREfromDB(cre)
                )
            )
            docs[group.name] = grp

            # then handle cre2 -> cre1 link
            if cre.name in docs.keys():
                c = docs[cre.name]
            else:
                c = CREfromDB(cre)
            docs[cre.name] = c
            # this cannot be grp, grp already has a link to cre2
            c.add_link(
                cre_defs.Link(
                    ltype=cre_defs.LinkTypes.from_str(type), document=CREfromDB(group)
                )
            )

        # external links are CRE -> standard
        for internal_doc, standard, type in self.__get_external_links():
            cr = None
            grp = None
            if internal_doc.name in docs.keys():
                cr = docs[internal_doc.name]
            else:
                cr = CREfromDB(internal_doc)
            if len(standard.name) != 0:
                docs[cr.name] = cr.add_link(
                    cre_defs.Link(
                        ltype=cre_defs.LinkTypes.from_str(type),
                        document=nodeFromDB(standard),
                    )
                )
        # unlinked cres next
        for ucre in self.__get_unlinked_cres():
            docs[ucre.name] = CREfromDB(ucre)

        # unlinked nodes last
        for unode in self.__get_unlinked_nodes():
            nde = nodeFromDB(unode)
            docs["%s-%s:%s:%s" % (nde.name, nde.doctype, nde.id, nde.description)] = nde
            logger.info(f"{nde.name} is unlinked?")

        if not dry_run:
            for _, doc in docs.items():
                title = ""
                if hasattr(doc, "id"):
                    title = (
                        doc.id.replace("/", "-")
                        .replace(" ", "_")
                        .replace('"', "")
                        .replace("'", "")
                        + ".yaml"
                    )
                elif hasattr(doc, "sectionID"):
                    title = (
                        doc.name
                        + "_"
                        + doc.sectionID.replace("/", "-")
                        .replace(" ", "_")
                        .replace('"', "")
                        .replace("'", "")
                        + ".yaml"
                    )
                else:
                    logger.fatal(
                        f"doc does not have neither sectionID nor id, this is a bug! {doc.__dict__}"
                    )
                file.writeToDisk(
                    file_title=title,
                    file_content=yaml.safe_dump(doc.todict()),
                    cres_loc=dir,
                )

        return list(docs.values())

    def all_cres_with_pagination(
        self, page: int = 1, per_page: int = 10
    ) -> List[cre_defs.CRE]:
        cres = self.session.query(CRE).paginate(
            page=int(page), per_page=per_page, error_out=False
        )
        total_pages = cres.pages
        result = self._hydrate_cres_batch(list(cres.items))
        return result, page, total_pages

    def get_cre_path(self, fromID: str, toID: str) -> List[cre_defs.Document]:
        if not self.graph:
            self.with_graph()

        fromDbID = (
            self.session.query(CRE.id).filter(CRE.external_id == fromID).first()[0]
        )
        toDbID = self.session.query(CRE.id).filter(CRE.external_id == toID).first()[0]

        forwardPath = self.graph.get_path(f"CRE: {fromDbID}", f"CRE: {toDbID}")
        backwardsPath = self.graph.get_path(f"CRE: {toDbID}", f"CRE: {fromDbID}")
        cres = []
        path = []
        if forwardPath:  # our graph is directed, so we need to check both paths
            path = forwardPath
        else:
            path = backwardsPath

        for entry in path:
            entryID = entry.replace("CRE: ", "")
            shallow_CRE = self.get_cre_by_db_id(entryID)

            if shallow_CRE:
                cres.append(self.get_CREs(external_id=shallow_CRE.id)[0])
        return cres

    def get_cre_hierarchy(self, cre: cre_defs.CRE) -> int:
        if not self.graph:
            self.with_graph()
        roots = self.get_root_cres()

        root_cre_ids = [r.id for r in roots]
        return self.graph.get_hierarchy(rootIDs=root_cre_ids, creID=cre.id)

    # def all_nodes_with_pagination(
    #     self, page: int = 1, per_page: int = 10
    # ) -> List[cre_defs.Document]:
    #     result: List[cre_defs.Node] = []
    #     nodes = self.session.query(Node).paginate(
    #         page=int(page), per_page=per_page, error_out=False
    #     )
    #     total_pages = nodes.pages
    #     for node in nodes.items:
    #         result.extend(
    #             self.get_nodes(
    #                 name=node.name,
    #                 section=node.section,
    #                 subsection=node.subsection,
    #                 sectionID=node.section_id,
    #             )
    #         )
    #     return result, page, total_pages

    def delete_nodes(self, node_name: str):
        entries = (
            self.session.query(Node)
            .filter(func.lower(Node.name) == node_name.lower())
            .all()
        )
        if not entries:
            logger.debug(f"Nodes {node_name} is already deleted")
            return

        for entry in entries:
            entry_links = self.session.query(Links).filter(Links.node == entry.id).all()
            for link in entry_links:
                self.session.delete(link)

            embeddings = self.get_embeddings_for_doc(nodeFromDB(entry))
            if embeddings:
                self.session.delete(embeddings)
            self.session.delete(entry)

        self.delete_gapanalysis_results_for(node_name)
        self.session.commit()

    def delete_gapanalysis_results_for(self, node_name):
        res = (
            self.session.query(GapAnalysisResults)
            .filter(GapAnalysisResults.cache_key.like(f"%{node_name}%"))
            .all()
        )
        for r in res:
            result = self.session.delete(r)
            if result:
                logger.info(f"deleted {result.rowcount} objects")
        self.session.commit()
        self.session.flush()
        return res

    def add_cre(self, cre: cre_defs.CRE) -> CRE:
        entry: CRE = None
        query = self.session.query(CRE).filter(func.lower(CRE.name) == cre.name.lower())
        if cre.id:
            entry = query.filter(CRE.external_id == cre.id).first()
        else:
            entry = query.filter(
                func.lower(CRE.description) == cre.description.lower()
            ).first()

        if entry is not None:
            logger.info(f"knew of CRE {cre.name} ,updating")
            if not entry.external_id:
                if entry.external_id != cre.id:
                    raise ValueError(
                        f"Attempting to register existing CRE"
                        f"{entry.external_id}:{entry.name} with other ID {cre.id}"
                    )
                entry.external_id = cre.id
            if not entry.description:
                entry.description = cre.description
            if not entry.tags:
                entry.tags = ",".join(cre.tags)
            return entry
        else:
            logger.info("did not know of cre %s ,adding" % cre.name)
            entry = CRE(
                description=cre.description,
                name=cre.name,
                external_id=cre.id,
                tags=",".join([str(t) for t in cre.tags]),
            )
            self.session.add(entry)
            self.session.commit()
            if self.graph:
                self.graph.add_cre(cre=cre)
        return entry

    def add_node(
        self, node: cre_defs.Node, comparison_skip_attributes: List = ["link"]
    ) -> Optional[Node]:
        if not node:
            raise ValueError("Node is None")

        dbnode = dbNodeFromNode(node)
        if not dbnode:
            logger.warning(f"{node} could not be transformed to a DB object")
            return None

        # Upsert by the SQL UNIQUE constraint identity:
        # (name, section, subsection, version, section_id).
        # Use object_select() as the single identity matching mechanism.
        #
        # We intentionally skip non-identity fields that can differ across
        # import stages (tags/description/type/link/metadata).
        skip = set(comparison_skip_attributes or [])
        skip.update(
            {
                "tags",
                "description",
                "ntype",
                "link",
                "metadata_json",
                "id",
            }
        )

        def _node_by_unique_identity(n: Node) -> Optional[Node]:
            return (
                self.session.query(Node)
                .filter(
                    sqla.and_(
                        Node.name == n.name,
                        Node.section == n.section,
                        Node.subsection == n.subsection,
                        Node.version == n.version,
                        Node.section_id == n.section_id,
                    )
                )
                .first()
            )

        entries = self.object_select(dbnode, skip_attributes=list(skip))
        if entries:
            entry = entries[0]
            logger.info(
                f"upsert node {entry.name}:{entry.section_id}:{entry.section}:{entry.version} "
                f"(section={entry.section}, subsection={entry.subsection})"
            )

            # Overwrite identity fields when the incoming document has values.
            if dbnode.section is not None and dbnode.section != entry.section:
                entry.section = dbnode.section
            if (
                getattr(dbnode, "subsection", None) is not None
                and dbnode.subsection != entry.subsection
            ):
                entry.subsection = dbnode.subsection
            if dbnode.version is not None and dbnode.version != entry.version:
                entry.version = dbnode.version

            if dbnode.section_id is not None and dbnode.section_id != entry.section_id:
                entry.section_id = dbnode.section_id

            # Only overwrite fields with meaningful values to avoid clobbering
            # existing classification tags.
            if dbnode.tags:
                entry.tags = dbnode.tags
            if dbnode.description:
                entry.description = dbnode.description
            if dbnode.link:
                entry.link = dbnode.link
            if dbnode.metadata_json:
                entry.metadata_json = dbnode.metadata_json

            try:
                self.session.commit()
            except IntegrityError as exc:
                self.session.rollback()
                # Upsert update can collide with an existing row on uq_node if
                # identity fields are normalized/filled from multiple sources.
                if "uq_node" in str(exc).lower():
                    existing = _node_by_unique_identity(dbnode)
                    if existing is not None:
                        logger.info(
                            "Recovered from uq_node collision during node upsert for %s:%s",
                            dbnode.name,
                            dbnode.section_id,
                        )
                        return existing
                raise
            return entry

        if not dbnode.ntype:
            logger.warning(f"{node} has no registered type, cannot add, skipping")
            return None

        logger.info(f"insert node {dbnode.name}:{dbnode.section}:{dbnode.section_id}")
        self.session.add(dbnode)
        try:
            self.session.commit()
        except IntegrityError as exc:
            self.session.rollback()
            if "uq_node" in str(exc).lower():
                existing = _node_by_unique_identity(dbnode)
                if existing is not None:
                    logger.info(
                        "Recovered from uq_node collision during node insert for %s:%s",
                        dbnode.name,
                        dbnode.section_id,
                    )
                    return existing
            raise
        if self.graph:
            self.graph.add_dbnode(dbnode=node)
        return dbnode

    def add_internal_link(
        self,
        higher: CRE,
        lower: CRE,
        ltype: cre_defs.LinkTypes,
    ) -> cre_defs.Link:
        """
        adds a link between two CREs in the database,
        Args:
            higher (CRE): the higher level CRE that CONTAINS or is RELATED to lower
            lower (CRE): the lower level CRE that is CONTAINED or is RELATED to higher
            ltype (cre_defs.LinkTypes, optional): the linktype
        Returns: the cre_defs.Link or None in case of error (cycle)
        """
        if ltype == None:
            raise ValueError("Every link should have a link type")

        if ltype == cre_defs.LinkTypes.PartOf:
            raise ValueError(
                "internal_link does not support 'PartOf' relationships, call it with higher/lower being opposite and the ltype being 'Contains' "
            )

        if lower.id is None:
            if lower.external_id is None:
                lower = (
                    self.session.query(CRE)
                    .filter(
                        sqla.and_(
                            CRE.name == lower.name, CRE.description == lower.description
                        )
                    )
                    .first()
                )
            else:
                lower = (
                    self.session.query(CRE)
                    .filter(
                        sqla.and_(
                            CRE.name == lower.name, CRE.external_id == lower.external_id
                        )
                    )
                    .first()
                )
        if higher.id is None:
            if higher.external_id is None:
                higher = (
                    self.session.query(CRE)
                    .filter(
                        sqla.and_(
                            CRE.name == higher.name,
                            CRE.description == higher.description,
                        )
                    )
                    .first()
                )
            else:
                higher = (
                    self.session.query(CRE)
                    .filter(
                        sqla.and_(
                            CRE.name == higher.name,
                            CRE.external_id == higher.external_id,
                        )
                    )
                    .first()
                )
        if lower is None or higher is None:
            logger.fatal(
                "Tried to insert internal mapping with element"
                " that doesn't exist in db, this looks like a bug"
            )
            return None

        entry_exists = (
            self.session.query(InternalLinks)
            .filter(
                sqla.or_(
                    sqla.and_(
                        InternalLinks.cre == lower.id, InternalLinks.group == higher.id
                    ),
                    sqla.and_(
                        InternalLinks.cre == higher.id, InternalLinks.group == lower.id
                    ),
                )
            )
            .first()
        )
        if entry_exists:
            # logger.info(
            #     f"knew of internal link {lower.name} == {higher.name} of ltype {entry_exists.ltype},"
            #     f"updating to ltype {ltype.value}"
            # )
            # entry_exists.ltype = ltype.value
            # self.session.commit()
            return cre_defs.Link(document=CREfromDB(lower), ltype=ltype)

        logger.info(
            "did not know of internal link"
            f" {higher.external_id}:{higher.name}"
            f" -> {lower.external_id}:{lower.name} of type {ltype.value},adding"
        )
        if not self.graph:
            logger.error("graph is null")
            raise ValueError(
                "internal CRE graph is None while importing, cannot detect cycles, this is unrecoverable"
            )

        higher_cre = CREfromDB(higher)
        lower_cre = CREfromDB(lower)
        link_to = cre_defs.Link(document=lower_cre, ltype=ltype)
        try:
            self.graph.add_link(doc_from=higher_cre, link_to=link_to)
            self.session.add(
                InternalLinks(type=ltype.value, cre=lower.id, group=higher.id)
            )
            self.session.commit()
            return cre_defs.Link(document=lower, ltype=ltype)
        except inmemory_graph.CycleDetectedError as cde:
            # cycle detected, do nothing
            pass

    def add_link(
        self,
        cre: CRE,
        node: Node,
        ltype: cre_defs.LinkTypes,
    ) -> None:
        if not ltype:
            raise ValueError("every link should have a link type")

        if cre.id is None:
            cre = (
                self.session.query(CRE).filter(sqla.and_(CRE.name == cre.name)).first()
            )
        if node.id is None:
            node = self.object_select(node=node)[0]

        entry = (
            self.session.query(Links)
            .filter(sqla.and_(Links.cre == cre.id, Links.node == node.id))
            .first()
        )
        if entry:
            logger.debug(
                f"knew of link {node.name}:{node.section}"
                f"=={cre.name} of type {entry.type},"
                f"updating type to {ltype.value}"
            )
            entry.type = ltype.value
            self.session.commit()
            return
        else:
            logger.debug(
                f"did not know of link {node.id})"
                f"{node.name}:{node.section}=={cre.id}){cre.name}"
                " ,adding"
            )
            self.session.add(Links(type=ltype.value, cre=cre.id, node=node.id))
            if self.graph:
                self.graph.add_link(
                    doc_from=CREfromDB(cre),
                    link_to=cre_defs.Link(document=nodeFromDB(node), ltype=ltype.value),
                )

        self.session.commit()

    def find_path_between_nodes(
        self, node_source_id: int, node_destination_id: int
    ) -> bool:
        """One line method to return paths in a graph,
        this starts getting complicated when we have more linktypes"""
        res: bool = nx.has_path(
            self.graph.graph.to_undirected(),
            "Node: " + str(node_source_id),
            "Node: " + str(node_destination_id),
        )

        return res

    def standards(self) -> List[str]:
        standards = (
            self.session.query(Node.name)
            .filter(Node.ntype == cre_defs.Credoctypes.Standard.value)
            .distinct()
        )
        return list(set([s[0] for s in standards]))

    def text_search(self, text: str) -> List[Optional[cre_defs.Document]]:
        """Given a piece of text, tries to find the best match
        for the text in the database.
        Shortcuts:
           'CRE:<id>' will search for the <id> in cre external ids
           'CRE:<name>' will search for the <name> in cre names
           '<Node type e.g. Standard>:<name>[:<section><sectionID>:<subsection>:<hyperlink>]' will search for
               all entries of <name> and optionally, section/subsection
           '\d\d\d-\d\d\d' (two sets of 3 digits) will first try to match
                CRE ids before it performs a free text search
           Anything else will be a case insensitive LIKE query in the database
        """
        # structured text search first
        cre_id_search = r"CRE(:| )(?P<id>\d+-\d+)"
        cre_naked_id_search = r"\d\d\d-\d\d\d"
        cre_name_search = r"CRE(:| )(?P<name>\w+)"
        types = "|".join([v.value for v in cre_defs.Credoctypes])
        node_search = (
            r"(Node|(?P<ntype>"
            + types
            + "))?((:| )?(?P<link>https?://\S+))?((:| )(?P<val>.+$))?"
        )
        match = re.search(cre_id_search, text, re.IGNORECASE)
        if match:
            return self.get_CREs(external_id=match.group("id"))

        match = re.search(cre_naked_id_search, text, re.IGNORECASE)
        if match:
            return self.get_CREs(external_id=match.group())

        match = re.search(cre_name_search, text, re.IGNORECASE)
        if match:
            return self.get_CREs(name=match.group("name"))

        match = re.search(node_search, text, re.IGNORECASE)
        if match:
            link = match.group("link")
            ntype = match.group("ntype")
            txt = match.group("val")
            results: List[cre_defs.Document] = []
            if txt:
                args = txt.split(":") if ":" in txt else txt.split(" ")
                if len(args) < 4:
                    args += [""] * (4 - len(args))
                s = set([p for p in permutations(args, 4)])
                for combo in s:
                    nodes = self.get_nodes(
                        name=combo[0],
                        section=combo[1],
                        subsection=combo[2],
                        link=link,
                        ntype=ntype,
                        sectionID=combo[3],
                    )
                    if nodes:
                        results.extend(nodes)
            elif link or ntype:
                nodes = self.get_nodes(link=link, ntype=ntype)
                if nodes:
                    results.extend(nodes)
            if results:
                return list(set(results))
        # fuzzy matches second
        args = [f"%{text}%", "", "", "", "", ""]
        results = {}
        s = set([p for p in permutations(args, 6)])
        for combo in s:
            nodes = self.get_nodes(
                name=combo[0],
                section=combo[1],
                subsection=combo[2],
                link=combo[3],
                description=combo[4],
                partial=True,
                ntype=None,  # type: ignore
                sectionID=combo[5],
            )
            if nodes:
                for node in nodes:
                    node_key = f"{node.name}:{node.version}:{node.section}:{node.sectionID}:{node.subsection}:"
                    results[node_key] = node
        args = [f"%{text}%", None, None]
        for combo in permutations(args, 3):
            cres = self.get_CREs(
                name=combo[0], external_id=combo[1], description=combo[2], partial=True
            )
            if cres:
                for cre in cres:
                    results[cre.id] = cre
        return list(results.values())

    def get_root_cres(self):
        """Returns CRES that only have "Contains" links"""
        # select name  from cre where cre.id not in (select cre from cre_links where type="Contains") and cre.id not in (select "group" from cre_links where type="Is Part OF");
        cres = (
            self.session.query(CRE)
            .filter(
                ~CRE.id.in_(
                    self.session.query(InternalLinks.cre).filter(
                        InternalLinks.type == cre_defs.LinkTypes.Contains,
                    )
                )
            )
            .filter(
                ~CRE.id.in_(
                    self.session.query(InternalLinks.group).filter(
                        InternalLinks.type == cre_defs.LinkTypes.PartOf,
                    )
                )
            )
            .all()
        )
        return self._hydrate_cres_batch(list(cres))

    def get_embeddings_by_doc_type(self, doc_type: str) -> Dict[str, List[float]]:
        res = {}
        embeddings = (
            self.session.query(Embeddings).filter(Embeddings.doc_type == doc_type).all()
        )
        if embeddings:
            for entry in embeddings:
                if doc_type == cre_defs.Credoctypes.CRE.value:
                    res[entry.cre_id] = [float(e) for e in entry.embeddings.split(",")]
                else:
                    res[entry.node_id] = [float(e) for e in entry.embeddings.split(",")]
        return res

    def get_embeddings_by_doc_type_paginated(
        self, doc_type: str, page: int = 1, per_page: int = 100
    ) -> Tuple[Dict[str, List[float]], int, int]:
        res = {}
        embeddings = (
            self.session.query(Embeddings)
            .filter(Embeddings.doc_type == doc_type)
            .paginate(page=int(page), per_page=per_page, error_out=False)
        )
        total_pages = embeddings.pages
        if embeddings.items:
            for entry in embeddings.items:
                if doc_type == cre_defs.Credoctypes.CRE.value:
                    res[entry.cre_id] = [float(e) for e in entry.embeddings.split(",")]
                else:
                    res[entry.node_id] = [float(e) for e in entry.embeddings.split(",")]
        return res, total_pages, page

    def get_embeddings_for_doc(self, doc: cre_defs.Node | cre_defs.CRE) -> Embeddings:
        if doc.doctype == cre_defs.Credoctypes.CRE:
            obj = self.session.query(CRE).filter(CRE.external_id == doc.id).first()
            return (
                self.session.query(Embeddings)
                .filter(Embeddings.cre_id == obj.id)
                .first()
            )
        else:
            node = dbNodeFromNode(doc)
            if not node:
                logger.warning(
                    f"cannot get embeddings for doc {doc.todict()} it's not translatable to a database document"
                )
                return None
            obj = self.object_select(node)
            if obj:
                return (
                    self.session.query(Embeddings)
                    .filter(Embeddings.node_id == obj[0].id)
                    .first()
                )

    def get_embedding(self, object_id: str) -> Optional[Embeddings]:
        return (
            self.session.query(Embeddings)
            .filter(
                sqla.or_(
                    Embeddings.cre_id == object_id, Embeddings.node_id == object_id
                )
            )
            .all()
        )

    def delete_all_embeddings(self) -> int:
        """
        Delete every row in ``embeddings`` so a subsequent ``PromptHandler(..., load_all_embeddings=True)``
        run rebuilds vectors (e.g. after changing smart-extract policy or embedding model).
        """
        n_deleted = self.session.query(Embeddings).delete(synchronize_session=False)
        self.session.commit()
        return int(n_deleted or 0)

    def add_embedding(
        self,
        db_object: CRE | Node,
        doctype: cre_defs.Credoctypes,
        embeddings: List[float],
        embedding_text: str,
        embeddings_url: Optional[str] = None,
    ):
        """
        Persist embedding vector and content.

        For nodes, ``embeddings_url`` is the resolved URL used for fetch/embed alignment
        (may include a fragment). When ``None``, defaults to ``db_object.link`` (importer hyperlink).
        """
        expected_dim_raw = (os.environ.get("CRE_EMBED_EXPECTED_DIM", "") or "").strip()
        embedding_model_id = (
            os.environ.get("CRE_EMBED_MODEL", "") or ""
        ).strip() or "gemini/gemini-embedding-001"
        embedding_dim = len(embeddings)
        if expected_dim_raw:
            expected_dim = int(expected_dim_raw)
            if len(embeddings) != expected_dim:
                raise ValueError(
                    f"embedding dimension mismatch for {db_object.id}: "
                    f"expected {expected_dim}, got {len(embeddings)}"
                )
        existing = self.get_embedding(db_object.id)
        embeddings_str = ",".join([str(e) for e in embeddings])
        resolved_node_url: Optional[str] = None
        if doctype != cre_defs.Credoctypes.CRE:
            resolved_node_url = (
                embeddings_url if embeddings_url is not None else db_object.link
            )

        if not existing:
            emb = None
            if doctype == cre_defs.Credoctypes.CRE:
                emb = Embeddings(
                    embeddings=embeddings_str,
                    cre_id=db_object.id,
                    doc_type=cre_defs.Credoctypes.CRE.value,
                    embeddings_content=embedding_text,
                    embedding_model_id=embedding_model_id,
                    embedding_dim=embedding_dim,
                )
            else:
                emb = Embeddings(
                    embeddings=embeddings_str,
                    node_id=db_object.id,
                    doc_type=db_object.ntype,
                    embeddings_content=embedding_text,
                    embeddings_url=resolved_node_url,
                    embedding_model_id=embedding_model_id,
                    embedding_dim=embedding_dim,
                )
            self.session.add(emb)
            self.session.commit()
            return emb
        else:
            logger.debug(f"knew of embedding for object {db_object.id} ,updating")
            existing[0].embeddings = embeddings_str
            existing[0].embeddings_content = embedding_text
            existing[0].embedding_model_id = embedding_model_id
            existing[0].embedding_dim = embedding_dim
            if doctype != cre_defs.Credoctypes.CRE:
                if embeddings_url is not None:
                    existing[0].embeddings_url = embeddings_url
                elif getattr(existing[0], "embeddings_url", None) is None:
                    existing[0].embeddings_url = db_object.link
            self.session.commit()

            return existing

    def assert_embedding_contract(
        self,
        *,
        expected_model_id: Optional[str],
        expected_dim: Optional[int],
    ) -> None:
        """
        Validate persisted embedding metadata consistency.

        - Fails when multiple dimensions are stored.
        - Fails when metadata is missing or mismatched against expected model/dimension.
        """
        rows = self.session.query(
            Embeddings.embedding_dim, Embeddings.embedding_model_id
        ).all()
        if not rows:
            return

        dims = {int(r[0]) for r in rows if r[0] is not None}
        model_ids = {str(r[1]) for r in rows if r[1]}
        has_missing_dim = any(r[0] is None for r in rows)
        has_missing_model = any(not r[1] for r in rows)

        if len(dims) > 1:
            raise RuntimeError(
                f"multiple embedding dimensions detected in DB: {sorted(dims)}"
            )
        if len(model_ids) > 1:
            raise RuntimeError(
                f"multiple embedding models detected in DB: {sorted(model_ids)}"
            )

        if has_missing_dim or has_missing_model:
            raise RuntimeError(
                "embedding metadata missing in DB; run metadata migration/backfill"
            )

        if expected_dim is not None and dims:
            db_dim = next(iter(dims))
            if db_dim != expected_dim:
                raise RuntimeError(
                    f"DB embedding dim {db_dim} does not match expected dim {expected_dim}"
                )

        if expected_model_id and model_ids:
            db_model = next(iter(model_ids))
            if db_model != expected_model_id:
                raise RuntimeError(
                    f"DB embedding model {db_model} does not match expected model {expected_model_id}"
                )

    def gap_analysis_exists(self, cache_key) -> bool:
        row = (
            self.session.query(GapAnalysisResults)
            .filter(GapAnalysisResults.cache_key == cache_key)
            .first()
        )
        if row is None:
            return False
        if gap_analysis_cache_key_is_primary(cache_key):
            return primary_gap_analysis_payload_is_material(row.ga_object)
        return True

    def get_gap_analysis_result(self, cache_key) -> str:
        logger.info(f"looking for gap analysis with cache key: {cache_key}")
        res = (
            self.session.query(GapAnalysisResults)
            .filter(GapAnalysisResults.cache_key == cache_key)
            .first()
        )
        if res:
            logger.info(f"found gap analysis with cache key: {cache_key}")
            return res.ga_object
        logger.info(f"did not find gap analysis with cache key: {cache_key}")

    def add_gap_analysis_result(self, cache_key: str, ga_object: str):
        existing = (
            self.session.query(GapAnalysisResults)
            .filter(GapAnalysisResults.cache_key == cache_key)
            .first()
        )
        if gap_analysis_cache_key_is_primary(cache_key):
            existing_payload = existing.ga_object if existing else None
            if not should_persist_primary_gap_analysis_cache(
                ga_object, existing_payload
            ):
                if existing is None:
                    logger.info(
                        "Skipping empty primary gap analysis cache insert for %s",
                        cache_key,
                    )
                else:
                    logger.warning(
                        "Refusing non-material primary gap analysis update for %s",
                        cache_key,
                    )
                return
        if existing:
            existing.ga_object = ga_object
            self.session.add(existing)
            self.session.commit()
        else:
            logger.info(f"adding gap analysis result with cache key: {cache_key}")
            res = GapAnalysisResults(cache_key=cache_key, ga_object=ga_object)
            self.session.add(res)
            self.session.commit()


def dbNodeFromNode(doc: cre_defs.Node) -> Optional[Node]:
    if doc.doctype == cre_defs.Credoctypes.Standard:
        return dbNodeFromStandard(doc)
    elif doc.doctype == cre_defs.Credoctypes.Code:
        return dbNodeFromCode(doc)
    elif doc.doctype == cre_defs.Credoctypes.Tool:
        return dbNodeFromTool(doc)
    else:
        return None


def dbNodeFromCode(code: cre_defs.Node) -> Node:
    code = cast(cre_defs.Code, code)
    tags = ""
    if code.tags:
        tags = ",".join(tags)
    return Node(
        name=code.name,
        ntype=code.doctype.value,
        tags=tags,
        description=code.description,
        link=code.hyperlink,
        metadata_json=code.metadata or {},
    )


def dbNodeFromStandard(standard: cre_defs.Node) -> Node:
    standard = cast(cre_defs.Standard, standard)
    tags = ""
    if standard.tags:
        tags = ",".join(standard.tags)
    return Node(
        name=standard.name,
        ntype=standard.doctype.value,
        tags=tags,
        description=standard.description,
        link=standard.hyperlink,
        section=standard.section,
        subsection=standard.subsection,
        version=standard.version,
        section_id=standard.sectionID,
        metadata_json=standard.metadata or {},
    )


def dbNodeFromTool(tool: cre_defs.Node) -> Node:
    tool = cast(cre_defs.Tool, tool)
    tgs = [tool.tooltype.value]
    if tool.tags:
        tgs.extend(tool.tags)
    tags = ",".join(tgs)
    return Node(
        name=tool.name,
        tags=tags,
        ntype=tool.doctype.value,
        description=tool.description,
        link=tool.hyperlink,
        section=tool.section,
        section_id=tool.sectionID,
        metadata_json=tool.metadata or {},
    )


def nodeFromDB(dbnode: Node) -> cre_defs.Node:
    if not dbnode:
        return None
    tags = []
    if dbnode.tags:
        tags = list(set(dbnode.tags.split(",")))
    metadata = dbnode.metadata_json or {}
    if dbnode.ntype == cre_defs.Standard.__name__:
        return cre_defs.Standard(
            name=dbnode.name,
            section=dbnode.section,
            subsection=dbnode.subsection,
            hyperlink=dbnode.link,
            tags=tags,
            version=dbnode.version,
            sectionID=dbnode.section_id,
            description=dbnode.description or "",
            metadata=metadata,
        )
    elif dbnode.ntype == cre_defs.Tool.__name__:
        ttype = cre_defs.ToolTypes.Unknown
        for tag in tags:
            if tag in cre_defs.ToolTypes:
                ttype = cre_defs.ToolTypes[tag]
                tags.remove(tag)
        return cre_defs.Tool(
            name=dbnode.name,
            hyperlink=dbnode.link,
            tags=tags,
            description=dbnode.description,
            tooltype=ttype,
            section=dbnode.section,
            sectionID=dbnode.section_id,
            metadata=metadata,
        )
    elif dbnode.ntype == cre_defs.Code.__name__:
        return cre_defs.Code(
            name=dbnode.name,
            hyperlink=dbnode.link,
            tags=tags,
            description=dbnode.description,
            metadata=metadata,
        )
    else:
        raise ValueError(
            f"Db node {dbnode.name} has an unrecognised ntype {dbnode.ntype}"
        )


def CREfromDB(dbcre: CRE) -> cre_defs.CRE:
    if not dbcre:
        return None
    tags = []
    if dbcre.tags:
        tags = list(set(dbcre.tags.split(",")))
    return cre_defs.CRE(
        name=dbcre.name,
        description=dbcre.description,
        id=dbcre.external_id,
        tags=tags,
        metadata=dbcre.metadata_json or {},
    )


def dbCREfromCRE(cre: cre_defs.CRE) -> CRE:
    tags = cre.tags if cre.tags else []
    return CRE(
        name=cre.name,
        description=cre.description,
        external_id=cre.id,
        tags=",".join(tags),
        metadata_json=cre.metadata or {},
    )


def gap_analysis(
    neo_db: NEO_DB,
    node_names: List[str],
    cache_key: str = "",
):
    if neo_db is None:
        neo_db = NEO_DB.instance()
    cre_db = Node_collection()
    base_standard, paths = neo_db.gap_analysis(node_names[0], node_names[1])
    logger.info(f"got db gap analysis for {'>>>'.join(node_names)}, calculating paths")
    if base_standard is None:
        return None
    grouped_paths = {}
    extra_paths_dict = {}
    GA_STRONG_UPPER_LIMIT = 2

    for node in base_standard:
        key = node.id
        if not key:
            logger.error(
                f"key is empty, this is a bug and this gap analysis will not progress"
            )
            continue
        if key not in grouped_paths:
            grouped_paths[key] = {"start": node, "paths": {}, "extra": 0}
            extra_paths_dict[key] = {"paths": {}}

    # Paths may start from CRE nodes that were not included in ``base_standard`` (or
    # ``base_standard`` entries were skipped due to empty ids). Seed roots from each
    # path's start so we never drop Neo paths or hit KeyError in the merge loop below.
    for path in paths:
        start_doc = path.get("start")
        if start_doc is None:
            continue
        key = getattr(start_doc, "id", "") or ""
        if not key:
            continue
        if key not in grouped_paths:
            grouped_paths[key] = {"start": start_doc, "paths": {}, "extra": 0}
            extra_paths_dict[key] = {"paths": {}}

    for path in paths:
        key = path["start"].id
        end_key = path["end"].id
        if not end_key:
            logger.error(
                f"end_key is empty, this is a bug and this gap analysis will not progress"
            )
            continue
        path["score"] = get_path_score(path)
        del path["start"]
        if (
            path["score"] <= GA_STRONG_UPPER_LIMIT
        ):  # strong paths, return them in the main object
            if end_key in extra_paths_dict[key]["paths"]:
                # if we found a shortest path to a node previously in the weak paths
                del extra_paths_dict[key]["paths"][end_key]
                grouped_paths[key]["extra"] -= 1
            if end_key in grouped_paths[key]["paths"]:
                if grouped_paths[key]["paths"][end_key]["score"] > path["score"]:
                    # if we found a shortest path to an existing strong path
                    grouped_paths[key]["paths"][end_key] = path
            else:
                grouped_paths[key]["paths"][end_key] = path
        else:
            if end_key in grouped_paths[key]["paths"]:
                continue
            if end_key in extra_paths_dict[key]:
                if extra_paths_dict[key]["paths"][end_key]["score"] > path["score"]:
                    extra_paths_dict[key]["paths"][end_key] = path
            else:
                extra_paths_dict[key]["paths"][end_key] = path
                grouped_paths[key]["extra"] += 1

    if cache_key == "":
        cache_key = make_resources_key(node_names)
    if not grouped_paths:
        if gap_analysis_cache_key_is_primary(cache_key):
            stale = (
                cre_db.session.query(GapAnalysisResults)
                .filter(
                    or_(
                        GapAnalysisResults.cache_key == cache_key,
                        GapAnalysisResults.cache_key.like(cache_key + "->%"),
                    )
                )
                .all()
            )
            for row in stale:
                cre_db.session.delete(row)
            if stale:
                cre_db.session.commit()
        logger.warning(
            "Not persisting gap analysis for %s: grouped_paths is empty "
            "(Neo likely had no parseable base standard or no paths). "
            "Re-run after Neo is populated so the pair is not locked in as an empty cache.",
            cache_key,
        )
        return (node_names, grouped_paths, extra_paths_dict)

    logger.info(f"got gap analysis paths for {'>>>'.join(node_names)}, storing result")
    cre_db.add_gap_analysis_result(
        cache_key=cache_key, ga_object=flask_json.dumps({"result": grouped_paths})
    )

    for key in extra_paths_dict:
        cre_db.add_gap_analysis_result(
            cache_key=make_subresources_key(node_names, key),
            ga_object=flask_json.dumps({"result": extra_paths_dict[key]}),
        )
    logger.info(f"stored gapa analysis for {'>>>'.join(node_names)}, successfully")
    return (node_names, grouped_paths, extra_paths_dict)