db.py

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

from pprint import pprint

from collections import Counter
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

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 (
    get_path_score,
    make_resources_key,
    make_subresources_key,
)


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="")

    __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

    __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_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_pair",
        ),
    )


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

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

    embeddings_url = sqla.Column(sqla.String, default="")
    embeddings_content = sqla.Column(sqla.String, default="")
    __table_args__ = (
        sqla.PrimaryKeyConstraint(
            embeddings,
            doc_type,
            cre_id,
            node_id,
            name="uq_entry",
        ),
    )


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 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 []
            ),
        )


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 "neo4j://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 = NeoNode.nodes.first_or_none(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 = NeoNode.nodes.first_or_none(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 = NeoNode.nodes.first_or_none(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):
        """
        Gap analysis with feature toggle support.

        Toggle between original exhaustive traversal (default) and
        optimized tiered pruning (opt-in via GAP_ANALYSIS_OPTIMIZED env var).
        """
        from application.config import Config

        if Config.GAP_ANALYSIS_OPTIMIZED:
            logger.info(
                f"Gap Analysis: Using OPTIMIZED tiered pruning for {name_1}>>{name_2}"
            )
            return self._gap_analysis_optimized(name_1, name_2)
        else:
            logger.info(
                f"Gap Analysis: Using ORIGINAL exhaustive traversal for {name_1}>>{name_2}"
            )
            return self._gap_analysis_original(name_1, name_2)

    @classmethod
    def _gap_analysis_optimized(self, name_1, name_2):
        """
        OPTIMIZED: Tiered Pruning Strategy with Early Exit

        Tier 1: Strong links only (LINKED_TO, SAME, AUTOMATICALLY_LINKED_TO)
        Tier 2: Add hierarchical (CONTAINS) if Tier 1 empty
        Tier 3: Fallback to wildcard if both tiers empty
        """
        logger.info(
            f"Performing OPTIMIZED GraphDB queries for gap analysis {name_1}>>{name_2}"
        )
        base_standard = NeoStandard.nodes.filter(name=name_1)
        denylist = ["Cross-cutting concerns"]

        # 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."
            )
            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 _gap_analysis_original(self, name_1, name_2):
        """
        ORIGINAL: Exhaustive traversal (always runs both queries)

        This is the safe default - maintains backward compatibility.
        """
        logger.info(
            f"Performing ORIGINAL 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

        # Query 1: Wildcard (all relationships)
        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,
        )

        # Query 2: Filtered (LINKED_TO, AUTOMATICALLY_LINKED_TO, CONTAINS)
        path_records, _ = db.cypher_query(
            """
         MATCH (BaseStandard:NeoStandard {name: $name1})
         MATCH (CompareStandard:NeoStandard {name: $name2})
         MATCH p = allShortestPaths((BaseStandard)-[:(LINKED_TO|AUTOMATICALLY_LINKED_TO|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,
        )

        # Combine results (original behavior)
        def format_segment(seg: StructuredRel, nodes):
            relation_map = {
                RelatedRel: "RELATED",
                ContainsRel: "CONTAINS",
                LinkedToRel: "LINKED_TO",
                AutoLinkedToRel: "AUTOMATICALLY_LINKED_TO",
            }
            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]

            return {
                "start": NEO_DB.parse_node_no_links(start_node),
                "end": NEO_DB.parse_node_no_links(end_node),
                "relationship": relation_map[type(seg)],
            }

        def format_path_record(rec):
            return {
                "start": NEO_DB.parse_node_no_links(rec.start_node),
                "end": NEO_DB.parse_node_no_links(rec.end_node),
                "path": [format_segment(seg, rec.nodes) for seg in rec.relationships],
            }

        return [NEO_DB.parse_node_no_links(rec) for rec in base_standard], [
            format_path_record(rec[0]) for rec in (path_records + path_records_all)
        ]

    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 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")

            return {
                "start": NEO_DB.parse_node_no_links(start_node),
                "end": NEO_DB.parse_node_no_links(end_node),
                "relationship": rtype,
            }

        def format_path_record(rec):
            return {
                "start": NEO_DB.parse_node_no_links(rec.start_node),
                "end": NEO_DB.parse_node_no_links(rec.end_node),
                "path": [format_segment(seg, rec.nodes) for seg in rec.relationships],
            }

        return [NEO_DB.parse_node_no_links(rec) for rec in base_standard], [
            format_path_record(rec[0]) for rec in path_records
        ]

    @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 not os.environ.get("NO_LOAD_GRAPH_DB"):
            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):
                if v:
                    attr = getattr(Node, vk)
                    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
        return self.get_CREs(external_id=external_id[0])[0]

    def list_node_ids_by_ntype(self, ntype: str) -> List[str]:
        return self.session.query(Node.id).filter(Node.ntype == ntype).all()

    def list_node_ids_by_name(self, name: str) -> List[str]:
        return self.session.query(Node.id).filter(Node.name == name).all()

    def list_cre_ids(self) -> List[str]:
        return self.session.query(CRE.id).all()

    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]:
        cres: 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 []

        for matching_cre in dbcres:
            cre = CREfromDB(matching_cre)
            cre.links = self.__make_cre_links(
                cre=matching_cre, include_only_nodes=include_only
            )
            cre.links.extend(self.__make_cre_internal_links(cre=matching_cre))
            cres.append(cre)
        return cres

    def __make_cre_internal_links(self, cre: CRE) -> List[cre_defs.Link]:
        links = []
        internal_links = (
            self.session.query(InternalLinks)
            .filter(
                sqla.or_(InternalLinks.cre == cre.id, InternalLinks.group == cre.id)
            )
            .all()
        )

        if len(internal_links) == 0:
            logger.debug(
                f"CRE {cre.name}:{cre.external_id}:{cre.id} has no internal links"
            )

        for internal_link in internal_links:

            linked_cre_query = self.session.query(CRE)
            link_type = cre_defs.LinkTypes.from_str(internal_link.type)

            if internal_link.cre == cre.id:
                # if we are the lower cre in this relationship, we need to flip the "Contains" linktypes
                linked_cre = linked_cre_query.filter(
                    CRE.id == internal_link.group
                ).first()  # get the higher cre so we can add the link
                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
                ):  # if it's not a "Contains" link, it's a "Related" link
                    links.append(
                        cre_defs.Link(ltype=link_type, document=CREfromDB(linked_cre))
                    )
                continue
            # if we are are the higher cre then we don't need to do anything, relationship types are always "higher"->"lower"
            linked_cre = linked_cre_query.filter(CRE.id == internal_link.cre).first()
            links.append(cre_defs.Link(ltype=link_type, document=CREfromDB(linked_cre)))
        return links

    def __make_cre_links(
        self, cre: CRE, include_only_nodes: List[str]
    ) -> List[cre_defs.Link]:
        links = []
        for link in self.session.query(Links).filter(Links.cre == cre.id).all():
            node = self.session.query(Node).filter(Node.id == link.node).first()
            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]:
        result: List[cre_defs.CRE] = []
        cres = self.session.query(CRE).paginate(
            page=int(page), per_page=per_page, error_out=False
        )
        total_pages = cres.pages
        for cre in cres.items:
            result.extend(self.get_CREs(external_id=cre.external_id))
        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(f"Node is None")
            return None
        dbnode = dbNodeFromNode(node)
        if not dbnode:
            logger.warning(f"{node} could not be transformed to a DB object")
            return None
        if not dbnode.ntype:
            logger.warning(f"{node} has no registered type, cannot add, skipping")
            return None
        entries = self.object_select(dbnode, skip_attributes=comparison_skip_attributes)
        if entries:
            entry = entries[0]
            logger.info(
                f"knew of node {entry.name}:{entry.section_id}:{entry.section}:{entry.link} ,updating"
            )
            if node.section and node.section != entry.section:
                entry.section = node.section
            entry.link = node.hyperlink
            self.session.commit()
            return entry
        else:
            logger.info(
                f"did not know of node {dbnode.name}:{dbnode.section}:{dbnode.section_id} ,adding"
            )
            self.session.add(dbnode)
            self.session.commit()
            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)
            .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()
        )
        result = []
        for c in cres:
            result.extend(self.get_CREs(external_id=c.external_id))
        return result

    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 add_embedding(
        self,
        db_object: CRE | Node,
        doctype: cre_defs.Credoctypes,
        embeddings: List[float],
        embedding_text: str,
    ):
        existing = self.get_embedding(db_object.id)
        embeddings_str = ",".join([str(e) for e in embeddings])

        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,
                )
            else:
                emb = Embeddings(
                    embeddings=embeddings_str,
                    node_id=db_object.id,
                    doc_type=db_object.ntype,
                    embeddings_content=embedding_text,
                    embeddings_url=db_object.link,
                )
            self.session.add(emb)
            self.session.commit()
            return emb
        else:
            logger.debug(f"knew of embedding for object {db_object.id} ,updating")
            self.session.commit()
            existing[0].embeddings = embeddings_str
            existing[0].embeddings_content = embedding_text
            self.session.commit()

            return existing

    def gap_analysis_exists(self, cache_key) -> bool:
        q = self.session.query(GapAnalysisResults).filter(
            GapAnalysisResults.cache_key == cache_key
        )
        return self.session.query(q.exists()).scalar()

    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):
        if not self.gap_analysis_exists(cache_key):
            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,
    )


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,
        # str() guard: sectionID must reach the DB column as a string.
        # Protects against any float that slipped through upstream coercion.
        section_id=str(standard.sectionID) if standard.sectionID is not None else "",
    )


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,
    )


def nodeFromDB(dbnode: Node) -> cre_defs.Node:
    if not dbnode:
        return None
    tags = []
    if dbnode.tags:
        tags = list(set(dbnode.tags.split(",")))
    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,
        )
    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,
        )
    elif dbnode.ntype == cre_defs.Code.__name__:
        return cre_defs.Code(
            name=dbnode.name,
            hyperlink=dbnode.link,
            tags=tags,
            description=dbnode.description,
        )
    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
    )


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),
    )


def gap_analysis(
    neo_db: NEO_DB,
    node_names: List[str],
    cache_key: str = "",
):
    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": {}}

    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)
    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)