import datetime
import hashlib
import json
import re
import typing
import zlib
from collections import namedtuple
from typing import List

from sqlalchemy import (
    JSON,
    BigInteger,
    Boolean,
    Column,
    DateTime,
    Enum,
    Float,
    ForeignKey,
    ForeignKeyConstraint,
    Index,
    Integer,
    LargeBinary,
    Sequence,
    String,
    Text,
    event,
    func,
    or_,
)
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.orm import joinedload, relationship, synonym

from anchore_engine.common.models.policy_engine import CVSS, NVDReference
from anchore_engine.db.entities.common import anchore_now_datetime, truncate_index_name
from anchore_engine.util.apk import compare_versions as apkg_compare_versions
from anchore_engine.util.deb import compare_versions as dpkg_compare_versions
from anchore_engine.util.langpack import compare_versions as langpack_compare_versions
from anchore_engine.util.rpm import compare_versions as rpm_compare_versions
from anchore_engine.utils import ensure_bytes, ensure_str

try:
    from anchore_engine.subsys import logger as log
except:
    import logging

    logger = logging.getLogger(__name__)
    log = logger

from .common import Base, StringJSON, UtilMixin, get_thread_scoped_session

DistroTuple = namedtuple("DistroTuple", ["distro", "version", "flavor"])

base_score_key = "base_score"
exploitability_score_key = "exploitability_score"
impact_score_key = "impact_score"
base_metrics_key = "base_metrics"
cvss_v3_key = "cvss_v3"
cvss_v2_key = "cvss_v2"


# Feeds
class FeedMetadata(Base, UtilMixin):
    __tablename__ = "feeds"

    name = Column(String, primary_key=True)
    description = Column(String)
    access_tier = Column(Integer)
    groups = relationship(
        "FeedGroupMetadata", back_populates="feed", cascade="all, delete-orphan"
    )
    last_full_sync = Column(DateTime)
    last_update = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    enabled = Column(Boolean, default=True)

    @classmethod
    def get_by_name(cls, session, name):
        return session.query(FeedMetadata).filter(FeedMetadata.name == name).scalar()

    def __repr__(self):
        return "<{}(name={}, access_tier={}, enabled={}, created_at={}>".format(
            self.__class__,
            self.name,
            self.access_tier,
            self.enabled,
            self.created_at.isoformat(),
        )

    def to_json(self, include_groups=True):
        j = super().to_json()

        if include_groups:
            j["groups"] = [g.to_json() for g in self.groups]
        else:
            j["groups"] = None

        return j


class FeedGroupMetadata(Base, UtilMixin):
    __tablename__ = "feed_groups"

    name = Column(String, primary_key=True)
    feed_name = Column(String, ForeignKey(FeedMetadata.name), primary_key=True)
    description = Column(String)
    access_tier = Column(Integer)
    last_sync = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    last_update = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )
    enabled = Column(Boolean, default=True)
    count = Column(
        BigInteger
    )  # To cache the row count of the group between feed syncs to avoid extra row count ops
    feed = relationship("FeedMetadata", back_populates="groups")

    def __repr__(self):
        return (
            "<{} name={}, feed={}, access_tier={}, enabled={}, created_at={}>".format(
                self.__class__,
                self.name,
                self.feed_name,
                self.access_tier,
                self.enabled,
                self.created_at,
            )
        )

    def to_json(self, include_feed=False):
        j = super().to_json()
        if include_feed:
            j["feed"] = self.feed.to_json(include_groups=False)  # Avoid the loop
        else:
            j["feed"] = None  # Ensure no non-serializable stuff

        return j


class GrypeDBFeedMetadata(Base):
    """
    A data model for persisting the current active grype db that the system should use across all policy-engine instances
    Each instance of policy engine witll use the active record in this table to determine the correct grype db
    Primary key is checksum, which refers to the checksum of the tar file
    The object url points to the location in object storage that the tar file is stored. This is used by processes that sync
    There should only ever be a single active record. More than one indicates an error in the system
    """

    __tablename__ = "grype_db_feed_metadata"

    archive_checksum = Column(String, primary_key=True)
    db_checksum = Column(String, nullable=True)
    schema_version = Column(String, nullable=False)
    object_url = Column(String, nullable=False)
    active = Column(Boolean, nullable=False)
    built_at = Column(DateTime, nullable=False)
    created_at = Column(DateTime, default=anchore_now_datetime, nullable=False)
    last_updated = Column(
        DateTime,
        default=anchore_now_datetime,
        onupdate=anchore_now_datetime,
        nullable=False,
    )
    synced_at = Column(DateTime, nullable=True)
    groups = Column(JSONB, default=[])

    __table_args__ = (
        Index(
            truncate_index_name("ix_grype_db_feed_metadata_db_checksum"), db_checksum
        ),
        {},
    )


class GenericFeedDataRecord(Base):
    """
    A catch-all record for feed data without a specific schema mapping
    """

    __tablename__ = "feed_group_data"

    feed = Column(String, primary_key=True)
    group = Column(String, primary_key=True)
    id = Column(String, primary_key=True)
    created_at = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )
    updated_at = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )
    data = Column(
        StringJSON, nullable=False
    )  # TODO: make this a JSON type for dbs that support it


class GemMetadata(Base):
    __tablename__ = "feed_data_gem_packages"

    name = Column(String, primary_key=True)
    id = Column(BigInteger)
    latest = Column(String)
    licenses_json = Column(StringJSON)
    authors_json = Column(StringJSON)
    versions_json = Column(StringJSON)
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    def __repr__(self):
        return "<{} name={}, id={}, created_at={}>".format(
            self.__class__, self.name, self.id, self.created_at
        )

    def key_tuple(self):
        return self.name


class NpmMetadata(Base):
    __tablename__ = "feed_data_npm_packages"

    name = Column(String, primary_key=True)
    sourcepkg = Column(String)
    lics_json = Column(StringJSON)
    origins_json = Column(StringJSON)
    latest = Column(String)
    versions_json = Column(StringJSON)
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    def __repr__(self):
        return "<{} name={}, sourcepkg={}, created_at={}>".format(
            self.__class__, self.name, self.sourcepkg, self.created_at
        )

    def key_tuple(self):
        return self.name


class Vulnerability(Base):
    """
    A vulnerability/CVE record. Can come from many sources. Includes some specific fields and also a general
    metadata field that is json encoded string
    """

    __tablename__ = "feed_data_vulnerabilities"

    id = Column(String, primary_key=True)  # CVE Id, RHSA id, etc
    namespace_name = Column(String, primary_key=True)  # e.g. centos, rhel, "debian"
    severity = Column(
        Enum(
            "Unknown",
            "Negligible",
            "Low",
            "Medium",
            "High",
            "Critical",
            name="vulnerability_severities",
        ),
        nullable=False,
    )
    description = Column(Text, nullable=True)
    link = Column(String, nullable=True)
    metadata_json = Column(StringJSON, nullable=True)
    cvss2_vectors = Column(String, nullable=True)
    cvss2_score = Column(Float, nullable=True)
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )
    vulnerable_in = relationship(
        "VulnerableArtifact", back_populates="parent", cascade="all, delete-orphan"
    )
    fixed_in = relationship(
        "FixedArtifact", back_populates="parent", cascade="all, delete-orphan"
    )

    @property
    def additional_metadata(self):
        if self.metadata_json:
            if isinstance(self.metadata_json, str):
                return json.loads(self.metadata_json)
            return self.metadata_json
        return None

    @additional_metadata.setter
    def additional_metadata(self, value):
        m = {}
        if value:
            if isinstance(value, str):
                m = json.loads(value)
            elif isinstance(value, dict):
                m.update(value)
            else:
                m = {"metadata": value}
        self.metadata_json = m
        # self.metadata_json = json.dumps(value)

    def __repr__(self):
        return "<{} id={}, namespace_name={}, severity={}, created_at={}>".format(
            self.__class__, self.id, self.namespace_name, self.severity, self.created_at
        )

    def current_package_vulnerabilities(self, db_session):
        """
        Return a list of all packages that are marked as vulnerable to this item
        :return: list of ImagePackageVulnerability objects
        """
        return (
            db_session.query(ImagePackageVulnerability)
            .filter(
                ImagePackageVulnerability.vulnerability_id == self.id,
                ImagePackageVulnerability.vulnerability_namespace_name
                == self.namespace_name,
            )
            .all()
        )

    def is_empty(self):
        """
        Can a package be vulnerable to this, or is it an empty definition.
        :return: boolean
        """
        # return not self.vulnerable_in and not self.fixed_in
        return not self.fixed_in

    def get_cvss_severity(self):
        sev = "Unknown"
        try:
            score = self.cvss2_score
            if score <= 3.9:
                sev = "Low"
            elif score <= 6.9:
                sev = "Medium"
            elif score <= 10.0:
                sev = "High"
            else:
                sev = "Unknown"
        except:
            sev = "Unknown"
        return sev

    def get_nvd_vulnerabilities(self, cvss_version=3, _nvd_cls=None, _cpe_cls=None):
        ret = []

        db = get_thread_scoped_session()
        if not _nvd_cls or not _cpe_cls:
            _nvd_cls, _cpe_cls = select_nvd_classes(db)

        try:
            cves = self.get_nvd_identifiers(_nvd_cls, _cpe_cls)
            nvd_records = db.query(_nvd_cls).filter(_nvd_cls.name.in_(cves)).all()
        except Exception as err:
            log.warn(
                "failed to gather NVD information for vulnerability due to exception: {}".format(
                    str(err)
                )
            )
            nvd_records = None

        if nvd_records:
            ret = nvd_records

        return ret

    def get_nvd_identifiers(self, _nvd_cls, _cpe_cls):
        cves = []
        try:
            if self.id.startswith("CVE-"):
                cves = [self.id]

            if self.metadata_json and self.metadata_json.get("CVE", []):
                for cve_el in self.metadata_json.get("CVE", []):
                    if type(cve_el) == dict:
                        # RHSA and ELSA internal elements are dicts
                        cve_id = cve_el.get("Name", None)
                    elif type(cve_el) == str:
                        # ALAS internal elements are just CVE ids
                        cve_id = cve_el
                    else:
                        cve_id = None

                    if cve_id and cve_id not in cves:
                        cves.append(cve_id)
        except Exception as err:
            log.warn(
                "failed to gather NVD information for vulnerability due to exception: {}".format(
                    str(err)
                )
            )

        return cves


class VulnerableArtifact(Base):
    """
    An entity affected by a vulnerability, typically an os or application package.
    Typically populated by CVEs with specific vulnerable packages enumerated.

    """

    __tablename__ = "feed_data_vulnerabilities_vulnerable_artifacts"

    vulnerability_id = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)
    name = Column(String, primary_key=True)
    version = Column(String, primary_key=True)
    version_format = Column(String)
    epochless_version = Column(String)
    include_previous_versions = Column(Boolean, default=True)
    parent = relationship("Vulnerability", back_populates="vulnerable_in")
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    # This is necessary for ensuring correct FK behavior against a composite foreign key
    __table_args__ = (
        ForeignKeyConstraint(
            columns=(vulnerability_id, namespace_name),
            refcolumns=(Vulnerability.id, Vulnerability.namespace_name),
        ),
        {},
    )

    def __repr__(self):
        return "<{} name={}, version={}, vulnerability_id={}, namespace_name={}, created_at={}>".format(
            self.__class__,
            self.name,
            self.version,
            self.vulnerability_id,
            self.namespace_name,
            self.updated_at,
        )

    def match_and_vulnerable(self, package_obj):
        """
        Given a VulnerableArtifact record, is the given package object a match indicating that the package is vulnerable.

        :param vuln_obj:
        :param package_obj:
        :param has_fix: boolean indicating if there is a corresponding fix record
        :return:
        """
        vuln_obj = self
        if not isinstance(vuln_obj, VulnerableArtifact):
            raise TypeError(
                "Expected a VulnerableArtifact type, got: {}".format(type(vuln_obj))
            )

        dist = DistroNamespace.for_obj(package_obj)
        flavor = dist.flavor

        # Double-check names
        if (
            vuln_obj.name != package_obj.name
            and vuln_obj.name != package_obj.normalized_src_pkg
        ):
            log.warn(
                "Name mismatch in vulnerable check. This should not happen: Fix: {}, Package: {}, Package_Norm_Src: {}, Package_Src: {}".format(
                    vuln_obj.name,
                    package_obj.name,
                    package_obj.normalized_src_pkg,
                    package_obj.src_pkg,
                )
            )
            return False

        # Is it a catch-all record? Explicit 'None' or 'all' versions indicate all versions of the named package are vulnerable.
        # Or is it an exact version match?
        if vuln_obj.epochless_version in ["all", "None"] or (
            package_obj.fullversion == vuln_obj.epochless_version
            or package_obj.version == vuln_obj.epochless_version
        ):
            return True
        else:
            return False


class FixedArtifact(Base):
    """
    A record indicating an artifact version that marks a fix for a vulnerability
    """

    __tablename__ = "feed_data_vulnerabilities_fixed_artifacts"

    vulnerability_id = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)
    name = Column(String, primary_key=True)
    version = Column(String, primary_key=True)
    version_format = Column(String)
    epochless_version = Column(String)
    include_later_versions = Column(Boolean, default=True)
    parent = relationship("Vulnerability", back_populates="fixed_in")
    vendor_no_advisory = Column(Boolean, default=False)
    fix_metadata = Column(StringJSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )
    fix_observed_at = Column(DateTime)

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(vulnerability_id, namespace_name),
            refcolumns=(Vulnerability.id, Vulnerability.namespace_name),
        ),
        {},
    )

    @staticmethod
    def _fix_observed_at_update(mapper, connection, target):
        if not target.fix_observed_at and target.version and target.version != "None":
            target.fix_observed_at = datetime.datetime.utcnow()

    @classmethod
    def __declare_last__(cls):
        event.listen(cls, "before_update", cls._fix_observed_at_update)
        event.listen(cls, "before_insert", cls._fix_observed_at_update)

    def __repr__(self):
        return "<{} name={}, version={}, vulnerability_id={}, namespace_name={}, created_at={}>".format(
            self.__class__,
            self.name,
            self.version,
            self.vulnerability_id,
            self.namespace_name,
            self.created_at,
        )

    def match_but_not_fixed(self, package_obj):
        """
        Does the FixedArtifact match the package as a vulnerability such that the fix indicates the package is *not* fixed and is
        therefore vulnerable.

        :param fix_obj: as FixedArtifact record
        :param package_obj: an ImagePackage record
        :return: True if the names match and the fix record indicates the package is vulnerable and not fixed. False if no match or fix is applied and no vulnerability match
        """
        fix_obj = self
        if not isinstance(fix_obj, FixedArtifact):
            raise TypeError(
                "Expected a FixedArtifact type, got: {}".format(type(fix_obj))
            )

        dist = DistroNamespace.for_obj(package_obj)
        flavor = dist.flavor
        log.spew(
            "Package: {}, Fix: {}, Flavor: {}".format(
                package_obj.name, fix_obj.name, flavor
            )
        )

        # Double-check names
        if (
            fix_obj.name != package_obj.name
            and fix_obj.name != package_obj.normalized_src_pkg
        ):
            log.warn(
                "Name mismatch in fix check. This should not happen: Fix: {}, Package: {}, Package_Norm_Src: {}, Package_Src: {}".format(
                    fix_obj.name,
                    package_obj.name,
                    package_obj.normalized_src_pkg,
                    package_obj.src_pkg,
                )
            )
            return False

        # Handle the case where there is no version, indicating no fix available, all versions are vulnerable.
        # Is it a catch-all record? Explicit 'None' versions indicate all versions of the named package are vulnerable.
        if fix_obj.version == "None":
            return True

        # Is the package older than the fix?
        if (
            flavor == "RHEL"
        ):  # compare full package version with full fixed-in version, epoch handled in compare fn. fixes issue-265
            if rpm_compare_versions(package_obj.fullversion, fix_obj.version) < 0:
                log.spew(
                    "rpm Compared: {} < {}: True".format(
                        package_obj.fullversion, fix_obj.version
                    )
                )
                return True
        elif (
            flavor == "DEB"
        ):  # compare full package version with full fixed-in version, epoch handled in compare fn. fixes issue-265
            if dpkg_compare_versions(package_obj.fullversion, "lt", fix_obj.version):
                log.spew(
                    "dpkg Compared: {} < {}: True".format(
                        package_obj.fullversion, fix_obj.version
                    )
                )
                return True
        elif (
            flavor == "ALPINE"
        ):  # compare full package version with epochless fixed-in version
            if apkg_compare_versions(
                package_obj.fullversion, "lt", fix_obj.epochless_version
            ):
                log.spew(
                    "apkg Compared: {} < {}: True".format(
                        package_obj.fullversion, fix_obj.epochless_version
                    )
                )
                return True

        if package_obj.pkg_type in ["java", "maven", "npm", "gem", "python", "js"]:
            if package_obj.pkg_type in ["java", "maven"]:
                pomprops = package_obj.get_pom_properties()
                if pomprops:
                    pkgkey = "{}:{}".format(
                        pomprops.get("groupId"), pomprops.get("artifactId")
                    )
                    pkgversion = pomprops.get("version", None)
                else:
                    pkgversion = package_obj.version
            else:
                pkgversion = package_obj.fullversion

            if langpack_compare_versions(
                fix_obj.version, pkgversion, language=package_obj.pkg_type
            ):
                return True

        # Newer or the same
        return False


class NvdMetadata(Base):
    __tablename__ = "feed_data_nvd_vulnerabilities"

    name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)  # e.g. nvddb:2018"
    severity = Column(
        Enum(
            "Unknown",
            "Negligible",
            "Low",
            "Medium",
            "High",
            "Critical",
            name="vulnerability_severities",
        ),
        nullable=False,
        primary_key=True,
    )
    vulnerable_configuration = Column(StringJSON)
    vulnerable_software = Column(StringJSON)
    summary = Column(String)
    cvss = Column(StringJSON)
    cvssv3 = None
    cvssv2 = None
    vulnerable_cpes = relationship(
        "CpeVulnerability", back_populates="parent", cascade="all, delete-orphan"
    )
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    def __repr__(self):
        return "<{} name={}, created_at={}>".format(
            self.__class__, self.name, self.created_at
        )

    @property
    def normalized_id(self):
        return self.name

    @property
    def description(self):
        return self.summary if self.summary else ""

    @property
    def references(self):
        return []

    def get_max_base_score_nvd(self, cvss_version=3):
        if cvss_version == 3:
            score = None
        elif cvss_version == 2:
            score = self.cvss.get(base_metrics_key, {}).get("score", None)
        else:
            log.warn(
                "invalid cvss version specified as input ({})".format(cvss_version)
            )
            score = None

        if score is None:
            ret = -1.0
        else:
            try:
                ret = float(score)
            except:
                ret = -1.0

        return ret

    def get_max_exploitability_score_nvd(self, cvss_version=3):
        return -1.0

    def get_max_impact_score_nvd(self, cvss_version=3):
        return -1.0

    def get_max_cvss_score_nvd(self, cvss_version=3):
        if cvss_version == 3:
            ret = {
                base_score_key: -1.0,
                exploitability_score_key: -1.0,
                impact_score_key: -1.0,
            }
        elif cvss_version == 2:
            ret = {
                base_score_key: self.get_max_base_score_nvd(cvss_version),
                exploitability_score_key: -1.0,
                impact_score_key: -1.0,
            }

        else:
            log.warn(
                "invalid cvss version specified as input ({})".format(cvss_version)
            )
            ret = {
                base_score_key: -1.0,
                exploitability_score_key: -1.0,
                impact_score_key: -1.0,
            }

        return ret

    def get_cvss_scores_nvd(self):
        ret = [
            {
                "id": self.name,
                cvss_v2_key: self.get_max_cvss_score_nvd(cvss_version=2),
                cvss_v3_key: self.get_max_cvss_score_nvd(cvss_version=3),
            }
        ]

        return ret

    def get_cvss_data_nvd(self):
        ret = [
            {
                "id": self.name,
                cvss_v2_key: self.cvssv2 if self.cvssv2 else None,
                cvss_v3_key: self.cvssv3 if self.cvssv3 else None,
            }
        ]

        return ret

    # vendor scores

    def get_max_base_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_exploitability_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_impact_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_cvss_score_vendor(self, cvss_version=3):
        ret = {
            base_score_key: -1.0,
            exploitability_score_key: -1.0,
            impact_score_key: -1.0,
        }
        return ret

    def get_cvss_scores_vendor(self):
        return []

    def get_cvss_data_vendor(self):
        return []

    @property
    def link(self):
        return "https://nvd.nist.gov/vuln/detail/{}".format(self.name)

    def key_tuple(self):
        return self.name

    def to_nvd_reference(self) -> NVDReference:
        """
        Returns an NVDReference object from this nvd vulnerability. Function to be used by non-nvd vulnerabilities for
        populating an nvd reference
        """

        return NVDReference(
            vulnerability_id=self.name,
            severity=self.severity,
            link=self.link,
            cvss=self.get_all_cvss(),
        )

    def get_all_cvss(self) -> List[CVSS]:
        """
        Returns a list of CVSS objects for this vulnerability
        """
        return [
            CVSS(
                version="2.0",
                base_score=self.get_max_base_score_nvd(2),
                exploitability_score=-1.0,
                impact_score=-1.0,
            )
        ]

    def get_all_nvd_references(self):
        """
        Compatibility method. Returns empty list since an nvd vuln doesn't have any nvd refrences
        """
        return []


class NvdV2Metadata(Base):
    __tablename__ = "feed_data_nvdv2_vulnerabilities"

    name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)  # e.g. nvddb:2018"
    severity = Column(
        Enum(
            "Unknown",
            "Negligible",
            "Low",
            "Medium",
            "High",
            "Critical",
            name="vulnerability_severities",
        ),
        nullable=False,
    )
    description = Column(String, nullable=True)
    cvss_v2 = Column(JSON, nullable=True)
    cvss_v3 = Column(JSON, nullable=True)
    link = Column(String, nullable=True)
    references = Column(JSON, nullable=True)
    vulnerable_cpes = relationship(
        "CpeV2Vulnerability", back_populates="parent", cascade="all, delete-orphan"
    )
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    __table_args__ = (
        Index(
            truncate_index_name("ix_feed_data_nvdv2_vulnerabilities_severity"), severity
        ),
        {},
    )

    def __repr__(self):
        return "<{} name={}, created_at={}>".format(
            self.__class__, self.name, self.created_at
        )

    @property
    def normalized_id(self):
        return self.name

    def _get_score(self, metric, score_key):
        if metric:
            score = metric.get(base_metrics_key).get(score_key, -1.0)
            try:
                score = float(score)
            except:
                score = -1.0
        else:
            score = -1.0

        return score

    def _get_metric(self, cvss_version=3):
        metric = None
        if cvss_version == 3:
            metric = self.cvss_v3
        elif cvss_version == 2:
            metric = self.cvss_v2
        else:
            log.warn(
                "invalid cvss version specified as input ({})".format(cvss_version)
            )

        return metric

    def get_max_base_score_nvd(self, cvss_version=3):
        metric = self._get_metric(cvss_version)
        return self._get_score(metric, base_score_key)

    def get_max_exploitability_score_nvd(self, cvss_version=3):
        metric = self._get_metric(cvss_version)
        return self._get_score(metric, exploitability_score_key)

    def get_max_impact_score_nvd(self, cvss_version=3):
        metric = self._get_metric(cvss_version)
        return self._get_score(metric, impact_score_key)

    def get_max_cvss_score_nvd(self, cvss_version=3):
        metric = self._get_metric(cvss_version)
        ret = {
            base_score_key: self._get_score(metric, base_score_key),
            exploitability_score_key: self._get_score(metric, exploitability_score_key),
            impact_score_key: self._get_score(metric, impact_score_key),
        }

        return ret

    def get_cvss_scores_nvd(self):
        ret = [
            {
                "id": self.name,
                cvss_v2_key: self.get_max_cvss_score_nvd(cvss_version=2),
                cvss_v3_key: self.get_max_cvss_score_nvd(cvss_version=3),
            }
        ]

        return ret

    def get_cvss_data_nvd(self):
        ret = [
            {
                "id": self.name,
                cvss_v2_key: self._get_metric(cvss_version=2),
                cvss_v3_key: self._get_metric(cvss_version=3),
            }
        ]

        return ret

    # vendor scores

    def get_max_base_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_exploitability_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_impact_score_vendor(self, cvss_version=3):
        return -1.0

    def get_max_cvss_score_vendor(self, cvss_version=3):
        ret = {
            base_score_key: -1.0,
            exploitability_score_key: -1.0,
            impact_score_key: -1.0,
        }
        return ret

    def get_cvss_scores_vendor(self):
        return []

    def get_cvss_data_vendor(self):
        return []

    def key_tuple(self):
        return self.name

    def to_nvd_reference(self) -> NVDReference:
        """
        Returns an NVDReference object from this nvd vulnerability. Function to be used by non-nvd vulnerabilities for
        populating an nvd reference

        cvss_v3 column format
        {
          "base_metrics": {
            "attack_complexity": "LOW",
            "attack_vector": "LOCAL",
            "availability_impact": "HIGH",
            "base_score": 7.8,
            "base_severity": "High",
            "confidentiality_impact": "HIGH",
            "exploitability_score": 1.8,
            "impact_score": 5.9,
            "integrity_impact": "HIGH",
            "privileges_required": "LOW",
            "scope": "UNCHANGED",
            "user_interaction": "NONE"
          },
          "vector_string": "CVSS:3.1/AV:L/AC:L/PR:L/UI:N/S:U/C:H/I:H/A:H",
          "version": "3.1"
        }

        cvss_v2 column format
        {
          "additional_information": {
            "ac_insuf_info": false,
            "obtain_all_privilege": false,
            "obtain_other_privilege": false,
            "obtain_user_privilege": false,
            "user_interaction_required": false
          },
          "base_metrics": {
            "access_complexity": "LOW",
            "access_vector": "LOCAL",
            "authentication": "NONE",
            "availability_impact": "COMPLETE",
            "base_score": 7.2,
            "confidentiality_impact": "COMPLETE",
            "exploitability_score": 3.9,
            "impact_score": 10,
            "integrity_impact": "COMPLETE"
          },
          "severity": "High",
          "vector_string": "AV:L/AC:L/Au:N/C:C/I:C/A:C",
          "version": "2.0"
        }
        """
        nvd_ref = NVDReference(
            vulnerability_id=self.name,
            severity=self.severity,
            link=self.link,
            cvss=self.get_all_cvss(),
        )

        return nvd_ref

    def get_all_cvss(self) -> List[CVSS]:
        """
        Returns a list of CVSS objects for this vulnerability
        """
        results = []
        v2_metric = self._get_metric(cvss_version=2)
        if v2_metric:
            results.append(
                CVSS(
                    version=v2_metric.get("version", "2.0"),
                    vector=v2_metric.get("vector_string"),
                    base_score=self._get_score(v2_metric, base_score_key),
                    exploitability_score=self._get_score(
                        v2_metric, exploitability_score_key
                    ),
                    impact_score=self._get_score(v2_metric, impact_score_key),
                )
            )

        v3_metric = self._get_metric(cvss_version=3)
        if v3_metric:
            results.append(
                CVSS(
                    version=v3_metric.get("version", "3.0"),
                    vector=v3_metric.get("vector_string"),
                    base_score=self._get_score(v3_metric, base_score_key),
                    exploitability_score=self._get_score(
                        v3_metric, exploitability_score_key
                    ),
                    impact_score=self._get_score(v3_metric, impact_score_key),
                )
            )

        return results

    def get_all_nvd_references(self):
        """
        Compatibility method. Returns empty list since an nvd vuln doesn't have any nvd refrences
        """
        return []


class VulnDBMetadata(Base):
    __tablename__ = "feed_data_vulndb_vulnerabilities"

    name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)  # e.g. vulndb:vulnerabilities
    severity = Column(
        Enum(
            "Unknown",
            "Negligible",
            "Low",
            "Medium",
            "High",
            "Critical",
            name="vulnerability_severities",
        ),
        nullable=False,
    )
    title = Column(String, nullable=True)
    description = Column(String, nullable=True)
    solution = Column(String, nullable=True)
    vendor_product_info = Column(JSON, nullable=True)
    references = Column(JSON, nullable=True)
    vulnerable_packages = Column(JSON, nullable=True)
    vulnerable_libraries = Column(JSON, nullable=True)
    vendor_cvss_v2 = Column(JSON, nullable=True)
    vendor_cvss_v3 = Column(JSON, nullable=True)
    nvd = Column(JSON, nullable=True)
    vuln_metadata = Column(JSON, nullable=True)
    cpes = relationship(
        "VulnDBCpe", back_populates="parent", cascade="all, delete-orphan"
    )
    # unaffected_cpes = relationship('VulnDBUnaffectedCpe', back_populates='parent', cascade='all, delete-orphan')
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    __table_args__ = (
        Index(
            truncate_index_name("ix_feed_data_vulndb_vulnerabilities_severity"),
            severity,
        ),
        {},
    )

    def __repr__(self):
        return "<{} name={}, created_at={}>".format(
            self.__class__, self.name, self.created_at
        )

    @property
    def normalized_id(self):
        """normalized_id will inspect the in coming external
        references and return a cve id in the case of a single
        match against vulndb information.
        """
        res = _get_one_or_none("source", "CVE ID", self.references)
        if res and res.get("url"):
            # findall should return a single id list ['2020-11989']
            cve_id_col = re.findall(r"\=(\d+\-\d+)", res.get("url"))
            if cve_id_col:
                return "CVE-" + cve_id_col[0]

        return self.name

    @property
    def referenced_cves(self) -> typing.List[str]:
        """
        Returns all referenced CVE IDs from the record, may be zero, one, or many.

        :return: List[Str]
        """
        ids = set()

        # References is a list of objects each with a potential "source" key
        for reference in [
            ref for ref in self.references if ref.get("source") == "CVE ID"
        ]:
            url = reference.get("url")
            if url:
                # findall should return a single id list ['2020-11989']
                cve_id_col = re.findall(r"\=(\d+\-\d+)", url)
                if cve_id_col:
                    ids.add("CVE-" + cve_id_col[0])

        return list(ids)

    def _get_max_cvss_v3_metric_nvd(self):
        cvss_v3 = None
        if self.nvd:
            if len(self.nvd) == 1:
                cvss_v3 = self.nvd[0].get(cvss_v3_key, None)
            else:
                max_score = None
                for nvd_item in self.nvd:
                    if nvd_item.get(cvss_v3_key, None):
                        if (
                            not max_score
                            or nvd_item.get(cvss_v3_key)
                            .get(base_metrics_key)
                            .get(base_score_key)
                            > max_score
                        ):
                            max_score = (
                                nvd_item.get(cvss_v3_key)
                                .get(base_metrics_key)
                                .get(base_score_key)
                            )
                            cvss_v3 = nvd_item.get(cvss_v3_key)
                        else:
                            continue
        return cvss_v3

    def _get_max_cvss_v2_metric_nvd(self):
        cvss_v2 = None
        if self.nvd:
            if len(self.nvd) == 1:
                cvss_v2 = self.nvd[0].get(cvss_v2_key, None)
            else:
                max_score = None
                for nvd_item in self.nvd:
                    if nvd_item.get(cvss_v2_key, None):
                        if (
                            not max_score
                            or nvd_item.get(cvss_v2_key)
                            .get(base_metrics_key)
                            .get(base_score_key)
                            > max_score
                        ):
                            max_score = (
                                nvd_item.get(cvss_v2_key)
                                .get(base_metrics_key)
                                .get(base_score_key)
                            )
                            cvss_v2 = nvd_item.get(cvss_v2_key)
                        else:
                            continue
        return cvss_v2

    def _get_max_cvss_metric_nvd(self, cvss_version):
        """
          [
            {
              "id": "CVE-2019-5440",
              "cvss_v2": {
                "version": "2.0",
                "vector_string": "AV:N/AC:M/Au:N/C:P/I:P/A:P",
                "base_metrics": {
                  "base_score": 6.8,
                  "exploitability_score": 8.6,
                  "impact_score": 6.4,
                  "severity": "Medium"
                  ...
                }
              },
              "cvss_v3": {
                "version": "3.0",
                "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
                "base_metrics": {
                  "base_score": 8.1,
                  "exploitability_score": 2.2,
                  "impact_score": 6.0,
                  "severity": "High"
                  ...
                }
              }
            },
            {
              "id": "CVE-2019-5441",
              "cvss_v2": {
                "version": "2.0",
                "vector_string": "AV:N/AC:M/Au:N/C:P/I:P/A:P",
                "base_metrics": {
                  "base_score": 6.8,
                  "exploitability_score": 8.6,
                  "impact_score": 6.4,
                  "severity": "Medium"
                  ...
                }
              },
              "cvss_v3": {
                "version": "3.0",
                "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
                "base_metrics": {
                  "base_score": 8.1,
                  "exploitability_score": 2.2,
                  "impact_score": 6.0,
                  "severity": "High"
                  ...
                }
              }
            },
          ]

        :param cvss_version:
        :return:
        """

        metric = None
        if cvss_version == 3:
            metric = self._get_max_cvss_v3_metric_nvd()
        elif cvss_version == 2:
            metric = self._get_max_cvss_v2_metric_nvd()
        else:
            log.warning(
                "invalid cvss version specified as input ({})".format(cvss_version)
            )

        return metric

    def _get_max_cvss_v3_metric_rbs(self):
        cvss_v3 = None
        if self.vendor_cvss_v3:
            if len(self.vendor_cvss_v3) == 1:
                cvss_v3 = self.vendor_cvss_v3[0]
            else:
                max_score = None
                for cvss_item in self.vendor_cvss_v3:
                    if (
                        not max_score
                        or cvss_item.get(base_metrics_key).get(base_score_key)
                        > max_score
                    ):
                        max_score = cvss_item.get(base_metrics_key).get(base_score_key)
                        cvss_v3 = cvss_item
                    else:
                        continue
        return cvss_v3

    def _get_highest_cvss_v2_rbs(self):
        cvss_v2 = None
        if self.vendor_cvss_v2:
            if len(self.vendor_cvss_v2) == 1:
                cvss_v2 = self.vendor_cvss_v2[0]
            else:
                max_score = None
                for cvss_item in self.vendor_cvss_v2:
                    if (
                        not max_score
                        or cvss_item.get(base_metrics_key).get(base_score_key)
                        > max_score
                    ):
                        max_score = cvss_item.get(base_metrics_key).get(base_score_key)
                        cvss_v2 = cvss_item
                    else:
                        continue
        return cvss_v2

    def _get_max_cvss_metric_rbs(self, cvss_version):
        """
          cvss_version is a list in format
          [
            {
              "version": "3.0",
              "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
              "base_metrics": {
                "base_score": 8.1,
                "exploitability_score": 2.2,
                "impact_score": 6.0,
                "severity": "High"
                ...
              }
            },
            {
              "version": "3.0",
              "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
              "base_metrics": {
                "base_score": 8.1,
                "exploitability_score": 2.2,
                "impact_score": 6.0,
                "severity": "High"
                ...
              }
            }
          ]
        :param cvss_version:
        :return:
        """

        metric = None
        if cvss_version == 3:
            metric = self._get_max_cvss_v3_metric_rbs()
        elif cvss_version == 2:
            metric = self._get_highest_cvss_v2_rbs()
        else:
            log.warning(
                "invalid cvss version specified as input ({})".format(cvss_version)
            )

        return metric

    def _get_score(self, metric, score_key):
        if metric:
            score = metric.get(base_metrics_key).get(score_key, -1.0)
            try:
                score = float(score)
            except:
                score = -1.0
        else:
            score = -1.0

        return score

    # nvd scores

    def get_max_base_score_nvd(self, cvss_version=3):
        metric = self._get_max_cvss_metric_nvd(cvss_version)
        return self._get_score(metric, base_score_key)

    def get_max_exploitability_score_nvd(self, cvss_version=3):
        metric = self._get_max_cvss_metric_nvd(cvss_version)
        return self._get_score(metric, exploitability_score_key)

    def get_max_impact_score_nvd(self, cvss_version=3):
        metric = self._get_max_cvss_metric_nvd(cvss_version)
        return self._get_score(metric, impact_score_key)

    def get_max_cvss_score_nvd(self, cvss_version=3):
        metric = self._get_max_cvss_metric_nvd(cvss_version)
        ret = {
            base_score_key: self._get_score(metric, base_score_key),
            exploitability_score_key: self._get_score(metric, exploitability_score_key),
            impact_score_key: self._get_score(metric, impact_score_key),
        }

        return ret

    def get_cvss_scores_nvd(self):
        result = []
        for nvd_cvss_item in self.get_cvss_data_nvd():
            """
            nvd_cvss_item is in the format
            {
              "id": "CVE-2019-5441",
              "cvss_v2": {
                "version": "2.0",
                "vector_string": "AV:N/AC:M/Au:N/C:P/I:P/A:P",
                "base_metrics": {
                  "base_score": 6.8,
                  "exploitability_score": 8.6,
                  "impact_score": 6.4,
                  "severity": "Medium"
                  ...
                }
              },
              "cvss_v3": {
                "version": "3.0",
                "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
                "base_metrics": {
                  "base_score": 8.1,
                  "exploitability_score": 2.2,
                  "impact_score": 6.0,
                  "severity": "High"
                  ...
                }
              }
            }
            """
            cvss_v2_metric = nvd_cvss_item.get(cvss_v2_key, None)
            cvss_v3_metric = nvd_cvss_item.get(cvss_v3_key, None)
            score_item = {
                "id": nvd_cvss_item.get("id"),
                cvss_v2_key: {
                    base_score_key: self._get_score(cvss_v2_metric, base_score_key),
                    exploitability_score_key: self._get_score(
                        cvss_v2_metric, exploitability_score_key
                    ),
                    impact_score_key: self._get_score(cvss_v2_metric, impact_score_key),
                },
                cvss_v3_key: {
                    base_score_key: self._get_score(cvss_v3_metric, base_score_key),
                    exploitability_score_key: self._get_score(
                        cvss_v3_metric, exploitability_score_key
                    ),
                    impact_score_key: self._get_score(cvss_v3_metric, impact_score_key),
                },
            }
            result.append(score_item)

        return result

    def get_cvss_data_nvd(self):
        return self.nvd if self.nvd else []

    # vendor scores

    def get_max_base_score_vendor(self, cvss_version=3):
        metric = self._get_max_cvss_metric_rbs(cvss_version)
        return self._get_score(metric, base_score_key)

    def get_max_exploitability_score_vendor(self, cvss_version=3):
        metric = self._get_max_cvss_metric_rbs(cvss_version)
        return self._get_score(metric, exploitability_score_key)

    def get_max_impact_score_vendor(self, cvss_version=3):
        metric = self._get_max_cvss_metric_rbs(cvss_version)
        return self._get_score(metric, impact_score_key)

    def get_max_cvss_score_vendor(self, cvss_version=3):
        metric = self._get_max_cvss_metric_rbs(cvss_version)
        ret = {
            base_score_key: self._get_score(metric, base_score_key),
            exploitability_score_key: self._get_score(metric, exploitability_score_key),
            impact_score_key: self._get_score(metric, impact_score_key),
        }

        return ret

    def get_cvss_scores_vendor(self):
        results = []

        if self.vendor_cvss_v2:
            for cvss_v2_item in self.vendor_cvss_v2:
                # create a new record for every single score as there could be a different number of v2 and v3 scores and its not clear which belong as a pair
                results.append(
                    {
                        "id": self.name,
                        cvss_v2_key: {
                            base_score_key: self._get_score(
                                cvss_v2_item, base_score_key
                            ),
                            exploitability_score_key: self._get_score(
                                cvss_v2_item, exploitability_score_key
                            ),
                            impact_score_key: self._get_score(
                                cvss_v2_item, impact_score_key
                            ),
                        },
                        cvss_v3_key: {
                            base_score_key: -1.0,
                            exploitability_score_key: -1.0,
                            impact_score_key: -1.0,
                        },
                    }
                )

        if self.vendor_cvss_v3:
            for cvss_v3_item in self.vendor_cvss_v3:
                # create a new record for every single score as there could be a different number of v2 and v3 scores and its not clear which belong as a pair
                results.append(
                    {
                        "id": self.name,
                        cvss_v2_key: {
                            base_score_key: -1.0,
                            exploitability_score_key: -1.0,
                            impact_score_key: -1.0,
                        },
                        cvss_v3_key: {
                            base_score_key: self._get_score(
                                cvss_v3_item, base_score_key
                            ),
                            exploitability_score_key: self._get_score(
                                cvss_v3_item, exploitability_score_key
                            ),
                            impact_score_key: self._get_score(
                                cvss_v3_item, impact_score_key
                            ),
                        },
                    }
                )

        return results

    def get_cvss_data_vendor(self):
        results = []

        if self.vendor_cvss_v2:
            for cvss_v2_item in self.vendor_cvss_v2:
                results.append(
                    {"id": self.name, cvss_v2_key: cvss_v2_item, cvss_v3_key: None}
                )

        if self.vendor_cvss_v3:
            for cvss_v3_item in self.vendor_cvss_v3:
                results.append(
                    {"id": self.name, cvss_v2_key: None, cvss_v3_key: cvss_v3_item}
                )

        return results

    @property
    def link(self):
        return None

    @property
    def vulnerable_cpes(self):
        return [cpe for cpe in self.cpes if cpe.is_affected]

    def key_tuple(self):
        return self.name

    def to_nvd_reference(self):
        """
        Compatibility method. Returns None since a VulnDB vulnerability cannot be coerced into NVDReference
        """
        return None

    def get_all_cvss(self) -> List[CVSS]:
        """
        Returns a list of CVSS objects from vendor_cvss_v2 and vendor_cvss_v3 columns of this vulnerability

        vendor_cvss_v3 column is in format
          [
            {
              "version": "3.0",
              "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
              "base_metrics": {
                "base_score": 8.1,
                "exploitability_score": 2.2,
                "impact_score": 6.0,
                "severity": "High"
                ...
              }
            }
          ]
        """
        results = []

        for v2_metric in self.vendor_cvss_v2:
            results.append(
                CVSS(
                    version=v2_metric.get("version", "2.0"),
                    vector=v2_metric.get("vector_string"),
                    base_score=self._get_score(v2_metric, base_score_key),
                    exploitability_score=self._get_score(
                        v2_metric, exploitability_score_key
                    ),
                    impact_score=self._get_score(v2_metric, impact_score_key),
                )
            )

        for v3_metric in self.vendor_cvss_v3:
            results.append(
                CVSS(
                    version=v3_metric.get("version", "3.0"),
                    vector=v3_metric.get("vector_string"),
                    base_score=self._get_score(v3_metric, base_score_key),
                    exploitability_score=self._get_score(
                        v3_metric, exploitability_score_key
                    ),
                    impact_score=self._get_score(v3_metric, impact_score_key),
                )
            )

        return results

    def get_all_nvd_references(self) -> List[NVDReference]:
        """
        Returns all NVDReferences object from this VulnDB vulnerability. Function to be used for populating the nvd references
        for VulnDB vulnerabilities only

        nvd column is in the format
          [
            {
              "id": "CVE-2019-5441",
              "cvss_v2": {
                "version": "2.0",
                "vector_string": "AV:N/AC:M/Au:N/C:P/I:P/A:P",
                "base_metrics": {
                  "base_score": 6.8,
                  "exploitability_score": 8.6,
                  "impact_score": 6.4,
                  "severity": "Medium"
                  ...
                }
              },
              "cvss_v3": {
                "version": "3.0",
                "vector_string": "CVSS:3.0/AV:N/AC:H/PR:N/UI:N/S:U/C:H/I:H/A:H",
                "base_metrics": {
                  "base_score": 8.1,
                  "exploitability_score": 2.2,
                  "impact_score": 6.0,
                  "severity": "High"
                  ...
                }
              }
            }
          ]
        """
        results = []
        for nvd_cvss_item in self.get_cvss_data_nvd():
            nvd_ref = NVDReference(vulnerability_id=nvd_cvss_item.get("id"), cvss=[])
            v2_metric = nvd_cvss_item.get(cvss_v2_key, None)
            if v2_metric:
                nvd_ref.cvss.append(
                    CVSS(
                        version=v2_metric.get("version", "2.0"),
                        vector=v2_metric.get("vector_string"),
                        base_score=self._get_score(v2_metric, base_score_key),
                        exploitability_score=self._get_score(
                            v2_metric, exploitability_score_key
                        ),
                        impact_score=self._get_score(v2_metric, impact_score_key),
                    )
                )

            v3_metric = nvd_cvss_item.get(cvss_v3_key, None)
            if v3_metric:
                nvd_ref.cvss.append(
                    CVSS(
                        version=v3_metric.get("version", "3.0"),
                        vector=v3_metric.get("vector_string"),
                        base_score=self._get_score(v3_metric, base_score_key),
                        exploitability_score=self._get_score(
                            v3_metric, exploitability_score_key
                        ),
                        impact_score=self._get_score(v3_metric, impact_score_key),
                    )
                )

            results.append(nvd_ref)

        return results


class CpeVulnerability(Base):
    __tablename__ = "feed_data_cpe_vulnerabilities"

    feed_name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)
    vulnerability_id = Column(String, primary_key=True)
    severity = Column(
        Enum(
            "Unknown",
            "Negligible",
            "Low",
            "Medium",
            "High",
            "Critical",
            name="vulnerability_severities",
        ),
        nullable=False,
        primary_key=True,
    )
    cpetype = Column(String, primary_key=True)
    vendor = Column(String, primary_key=True)
    name = Column(String, primary_key=True)
    version = Column(String, primary_key=True)
    update = Column(String, primary_key=True)
    meta = Column(String, primary_key=True)
    link = Column(String, nullable=True)
    parent = relationship("NvdMetadata", back_populates="vulnerable_cpes")
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    # This is necessary for ensuring correct FK behavior against a composite foreign key
    __table_args__ = (
        ForeignKeyConstraint(
            columns=(vulnerability_id, namespace_name, severity),
            refcolumns=(
                NvdMetadata.name,
                NvdMetadata.namespace_name,
                NvdMetadata.severity,
            ),
        ),
        Index(
            truncate_index_name("ix_feed_data_cpe_vulnerabilities_name_version"),
            name,
            version,
        ),
        Index(
            truncate_index_name("ix_feed_data_cpe_vulnerabilities_fk"),
            vulnerability_id,
            namespace_name,
            severity,
        ),
        {},
    )

    def __repr__(self):
        return "<{} feed_name={}, vulnerability_id={}, name={}, version={}, created_at={}>".format(
            self.__class__,
            self.feed_name,
            self.vulnerability_id,
            self.name,
            self.version,
            self.created_at.isoformat(),
        )

    def get_cpestring(self):
        ret = None
        try:
            final_cpe = ["cpe", "-", "-", "-", "-", "-", "-"]
            final_cpe[1] = self.cpetype
            final_cpe[2] = self.vendor
            final_cpe[3] = self.name
            final_cpe[4] = self.version
            final_cpe[5] = self.update
            final_cpe[6] = self.meta
            ret = ":".join(final_cpe)
        except:
            ret = None

        return ret

    def get_fixed_in(self):
        return []


class CpeV2Vulnerability(Base):
    __tablename__ = "feed_data_cpev2_vulnerabilities"

    feed_name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)
    vulnerability_id = Column(String, primary_key=True)
    part = Column(String, primary_key=True)
    vendor = Column(String, primary_key=True)
    product = Column(String, primary_key=True)
    name = synonym("product")
    version = Column(String, primary_key=True)
    update = Column(String, primary_key=True)
    edition = Column(String, primary_key=True)
    language = Column(String, primary_key=True)
    sw_edition = Column(String, primary_key=True)
    target_sw = Column(String, primary_key=True)
    target_hw = Column(String, primary_key=True)
    other = Column(String, primary_key=True)
    parent = relationship("NvdV2Metadata", back_populates="vulnerable_cpes")
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    # This is necessary for ensuring correct FK behavior against a composite foreign key
    __table_args__ = (
        ForeignKeyConstraint(
            columns=(vulnerability_id, namespace_name),
            refcolumns=(NvdV2Metadata.name, NvdV2Metadata.namespace_name),
        ),
        Index(
            truncate_index_name("ix_feed_data_cpev2_vulnerabilities_name_version"),
            product,
            version,
        ),
        Index(
            truncate_index_name("ix_feed_data_cpev2_vulnerabilities_fk"),
            vulnerability_id,
            namespace_name,
        ),
        {},
    )

    def __repr__(self):
        return "<{} feed_name={}, vulnerability_id={}, product={}, version={}, created_at={}>".format(
            self.__class__,
            self.feed_name,
            self.vulnerability_id,
            self.product,
            self.version,
            self.created_at.isoformat(),
        )

    def get_cpestring(self):
        ret = None
        try:
            final_cpe = ["cpe", "-", "-", "-", "-", "-", "-"]
            final_cpe[1] = "/" + self.part
            final_cpe[2] = self.vendor
            final_cpe[3] = self.product
            final_cpe[4] = self.version
            final_cpe[5] = self.update
            final_cpe[6] = self.other
            ret = ":".join(final_cpe)
        except:
            ret = None

        return ret

    def get_cpe23string(self):
        ret = None
        try:
            final_cpe = [
                "cpe",
                "2.3",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
            ]
            final_cpe[2] = self.part
            final_cpe[3] = self.vendor
            final_cpe[4] = self.product
            final_cpe[5] = self.version
            final_cpe[6] = self.update
            final_cpe[7] = self.edition
            final_cpe[8] = self.language
            final_cpe[9] = self.sw_edition
            final_cpe[10] = self.target_sw
            final_cpe[11] = self.target_hw
            final_cpe[12] = self.other
            ret = ":".join(final_cpe)
        except:
            ret = None

        return ret

    def get_fixed_in(self):
        return []


class VulnDBCpe(Base):
    __tablename__ = "feed_data_vulndb_cpes"

    feed_name = Column(String, primary_key=True)
    namespace_name = Column(String, primary_key=True)
    vulnerability_id = Column(String, primary_key=True)
    part = Column(String, primary_key=True)
    vendor = Column(String, primary_key=True)
    product = Column(String, primary_key=True)
    name = synonym("product")
    version = Column(String, primary_key=True)
    update = Column(String, primary_key=True)
    edition = Column(String, primary_key=True)
    language = Column(String, primary_key=True)
    sw_edition = Column(String, primary_key=True)
    target_sw = Column(String, primary_key=True)
    target_hw = Column(String, primary_key=True)
    other = Column(String, primary_key=True)
    is_affected = Column(Boolean, primary_key=True)
    parent = relationship("VulnDBMetadata", back_populates="cpes")
    created_at = Column(
        DateTime, default=datetime.datetime.utcnow
    )  # TODO: make these server-side
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    # This is necessary for ensuring correct FK behavior against a composite foreign key
    __table_args__ = (
        ForeignKeyConstraint(
            columns=(vulnerability_id, namespace_name),
            refcolumns=(VulnDBMetadata.name, VulnDBMetadata.namespace_name),
        ),
        Index(
            truncate_index_name("ix_feed_data_vulndb_affected_cpes_product_version"),
            product,
            version,
        ),
        Index(
            truncate_index_name("ix_feed_data_vulndb_affected_cpes_fk"),
            vulnerability_id,
            namespace_name,
        ),
        {},
    )

    def __repr__(self):
        return "<{} feed_name={}, vulnerability_id={}, product={}, version={}, created_at={}>".format(
            self.__class__,
            self.feed_name,
            self.vulnerability_id,
            self.product,
            self.version,
            self.created_at.isoformat(),
        )

    def get_cpestring(self):
        ret = None
        try:
            if self.sw_edition or self.target_sw or self.target_hw or self.other:
                edition = "~{}~{}~{}~{}~{}".format(
                    self.edition,
                    self.sw_edition,
                    self.target_sw,
                    self.target_hw,
                    self.other,
                )
            else:
                edition = self.edition

            uri_parts = [
                "cpe",
                "/" + self.part,
                self.vendor,
                self.product,
                self.version,
                self.update,
                edition,
                self.language,
            ]

            uri = ":".join(uri_parts)
            ret = uri.strip(":")  # remove any trailing :
        except:
            ret = None

        return ret

    def get_cpe23string(self):
        ret = None
        try:
            final_cpe = [
                "cpe",
                "2.3",
                self.part,
                self.vendor,
                self.product,
                self.version,
                self.update,
                self.edition,
                self.language,
                self.sw_edition,
                self.target_sw,
                self.target_hw,
                self.other,
            ]

            ret = ":".join(final_cpe)
        except:
            ret = None

        return ret

    def get_fixed_in(self):
        return [
            cpe.version
            for cpe in self.parent.cpes
            if not cpe.is_affected
            and cpe.product == self.product
            and cpe.vendor == self.vendor
            and cpe.part == self.part
        ]


# Analysis Data for Images


class ImagePackage(Base):
    """
    A package detected in an image by analysis
    """

    __tablename__ = "image_packages"

    image_id = Column(String, primary_key=True)
    image_user_id = Column(String, primary_key=True)

    name = Column(String, primary_key=True)
    version = Column(String, primary_key=True)
    pkg_type = Column(String, primary_key=True)  # RHEL, DEB, APK, etc.
    arch = Column(String, default="N/A", primary_key=True)
    pkg_path = Column(String, default="pkgdb", primary_key=True)

    pkg_path_hash = Column(String)  # The sha256 hash of the path in hex

    # Could pkg namespace be diff than os? e.g. rpms in Deb?
    distro_name = Column(String)
    distro_version = Column(String)
    like_distro = Column(String)

    fullversion = Column(String)
    release = Column(String, default="")
    origin = Column(String, default="N/A")
    src_pkg = Column(String, default="N/A")
    normalized_src_pkg = Column(String, default="N/A")

    metadata_json = Column(StringJSON)

    license = Column(String, default="N/A")
    size = Column(BigInteger, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    vulnerabilities = relationship(
        "ImagePackageVulnerability",
        back_populates="package",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )
    image = relationship("Image", back_populates="packages")
    pkg_db_entries = relationship(
        "ImagePackageManifestEntry",
        backref="package",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        Index(
            "ix_image_package_distronamespace",
            name,
            version,
            distro_name,
            distro_version,
            normalized_src_pkg,
        ),
        # TODO: add this index for feed sync performance, needs to be re-tested with new package usage
        #  Index('ix_image_package_distro_pkgs', distro_name, distro_version, name, normalized_src_pkg, version),
        {},
    )

    _distro_namespace = None

    @property
    def distro_namespace_meta(self):
        if not self._distro_namespace:
            self._distro_namespace = DistroNamespace.for_obj(self)
        return self._distro_namespace

    @property
    def distro_namespace(self):
        if self.distro_name and self.distro_version:
            return self.distro_name + ":" + self.distro_version
        else:
            return None

    def get_pom_properties(self):
        if not self.metadata_json:
            return None

        filebuf = self.metadata_json.get("pom.properties", "")
        props = {}
        for line in filebuf.splitlines():
            # line = anchore_engine.utils.ensure_str(line)
            if not re.match(r"\s*(#.*)?$", line):
                kv = line.split("=")
                key = kv[0].strip()
                value = "=".join(kv[1:]).strip()
                props[key] = value
        return props

    def find_vulnerabilities(self):
        """
        Given an ImagePackage object, return the vulnerabilities that it matches.

        :param package_obj:
        :return: list of Vulnerability objects
        """
        # ts = time.time()

        # package_obj = self
        log.debug(
            "Finding vulnerabilities for package: {} - {}".format(
                self.name, self.version
            )
        )

        matches = []
        db = get_thread_scoped_session()

        # decide what type of scan(s) to perform
        do_langscan = do_osscan = False

        pkgkey = pkgversion = None
        likematch = None
        if self.pkg_type in ["java", "maven"]:
            # search for maven hits
            if self.metadata_json:
                pombuf = self.metadata_json.get("pom.properties", "")
                if pombuf:
                    pomprops = self.get_pom_properties()
                    pkgkey = "{}:{}".format(
                        pomprops.get("groupId"), pomprops.get("artifactId")
                    )
                    pkgversion = pomprops.get("version", None)
                    likematch = "%java%"
                    do_langscan = True

        elif self.pkg_type in [
            "ruby",
            "gem",
            "npm",
            "js",
            "python",
            "nuget",
            "dotnet",
            "binary",
            "go",
        ]:
            pkgkey = self.name
            pkgversion = self.version
            if self.pkg_type in ["ruby", "gem"]:
                likematch = "%gem%"
                do_langscan = True
            elif self.pkg_type in ["npm", "js"]:
                likematch = "%npm%"
                do_langscan = True
            elif self.pkg_type in ["python"]:
                likematch = "%python%"
                do_langscan = True
            elif self.pkg_type in ["nuget", "dotnet"]:
                likematch = "%nuget%"
                do_langscan = True
            elif self.pkg_type in ["go"]:
                likematch = "%go%"
                do_langscan = True
            elif self.pkg_type in ["binary"]:
                likematch = "%binary%"
                do_langscan = True
        else:
            do_osscan = True

        if do_langscan:
            semvercount = (
                db.query(FixedArtifact)
                .filter(FixedArtifact.version_format == "semver")
                .count()
            )
            if semvercount:
                nslang = self.pkg_type
                log.debug(
                    "performing LANGPACK vuln scan {} - {}".format(pkgkey, pkgversion)
                )
                if pkgkey and pkgversion and likematch:
                    candidates = (
                        db.query(FixedArtifact)
                        .filter(FixedArtifact.name == pkgkey)
                        .filter(FixedArtifact.version_format == "semver")
                        .filter(FixedArtifact.namespace_name.like(likematch))
                    )
                    for candidate in candidates:
                        if (
                            candidate.vulnerability_id
                            not in [x.vulnerability_id for x in matches]
                        ) and (
                            langpack_compare_versions(
                                candidate.version, pkgversion, language=nslang
                            )
                        ):
                            matches.append(candidate)

        if do_osscan:
            log.debug("performing OS vuln scan {} - {}".format(self.name, self.version))

            dist = DistroNamespace(
                self.distro_name, self.distro_version, self.like_distro
            )
            namespace_name_to_use = dist.namespace_name

            # All options are the same, no need to loop
            if len(set(dist.like_namespace_names)) > 1:
                # Look for exact match first
                if (
                    not db.query(FeedGroupMetadata)
                    .filter(FeedGroupMetadata.name == dist.namespace_name)
                    .first()
                ):
                    # Check all options for distro/flavor mappings, stop at first with records present
                    for namespace_name in dist.like_namespace_names:
                        record_count = (
                            db.query(Vulnerability)
                            .filter(Vulnerability.namespace_name == namespace_name)
                            .count()
                        )
                        if record_count > 0:
                            namespace_name_to_use = namespace_name
                            break

            fix_candidates, vulnerable_candidates = self.candidates_for_package(
                namespace_name_to_use
            )

            for candidate in fix_candidates:
                # De-dup evaluations based on the underlying vulnerability_id. For packages where src has many binary builds, once we have a match we have a match.
                if candidate.vulnerability_id not in [
                    x.vulnerability_id for x in matches
                ] and candidate.match_but_not_fixed(self):
                    matches.append(candidate)

            for candidate in vulnerable_candidates:
                if candidate.vulnerability_id not in [
                    x.vulnerability_id for x in matches
                ] and candidate.match_and_vulnerable(self):
                    matches.append(candidate)

        # log.debug("TIMER DB: {}".format(time.time() - ts))
        return matches

    def candidates_for_package(self, distro_namespace=None):
        """
        Return all vulnerabilities for the named package with the specified distro. Will apply to any version
        of the package. If version is used, will filter to only those for the specified version.

        :param package_obj: the package to match against
        :param distro_namespace: the DistroNamespace object to match against (typically computed
        :return: List of Vulnerabilities
        """
        package_obj = self
        db = get_thread_scoped_session()

        if not distro_namespace:
            namespace_name = DistroNamespace.for_obj(package_obj).namespace_name
        else:
            namespace_name = distro_namespace

        # Match the namespace and package name or src pkg name
        fix_candidates = (
            db.query(FixedArtifact)
            .filter(
                FixedArtifact.namespace_name == namespace_name,
                or_(
                    FixedArtifact.name == package_obj.name,
                    FixedArtifact.name == package_obj.normalized_src_pkg,
                ),
            )
            .all()
        )

        # Match the namespace and package name or src pkg name
        vulnerable_candidates = (
            db.query(VulnerableArtifact)
            .filter(
                VulnerableArtifact.namespace_name == namespace_name,
                or_(
                    VulnerableArtifact.name == package_obj.name,
                    VulnerableArtifact.name == package_obj.normalized_src_pkg,
                ),
            )
            .all()
        )

        return fix_candidates, vulnerable_candidates


class ImagePackageManifestEntry(Base):
    """
    An entry from the package manifest (e.g. rpm, deb, apk) for verifying package contents in a generic way.

    """

    __tablename__ = "image_package_db_entries"

    # Package key
    image_id = Column(String, primary_key=True)
    image_user_id = Column(String, primary_key=True)
    pkg_name = Column(String, primary_key=True)
    pkg_version = Column(String, primary_key=True)
    pkg_type = Column(String, primary_key=True)  # RHEL, DEB, APK, etc.
    pkg_arch = Column(String, default="N/A", primary_key=True)
    pkg_path = Column(String, default="pkgdb", primary_key=True)

    # File path
    file_path = Column(String, primary_key=True)

    is_config_file = Column(Boolean, nullable=True)
    digest = Column(String)  # Will include a prefix: sha256, sha1, md5 etc.
    digest_algorithm = Column(String, nullable=True)
    file_group_name = Column(String, nullable=True)
    file_user_name = Column(String, nullable=True)
    mode = Column(Integer, nullable=True)  # Mode as an integer in decimal, not octal
    size = Column(Integer, nullable=True)

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(
                image_id,
                image_user_id,
                pkg_name,
                pkg_version,
                pkg_type,
                pkg_arch,
                pkg_path,
            ),
            refcolumns=(
                "image_packages.image_id",
                "image_packages.image_user_id",
                "image_packages.name",
                "image_packages.version",
                "image_packages.pkg_type",
                "image_packages.arch",
                "image_packages.pkg_path",
            ),
        ),
        {},
    )


NPM_SEQ = Sequence("image_npms_seq_id_seq", metadata=Base.metadata)


class ImageNpm(Base):
    """
    NOTE: This is a deprecated class used for legacy support and upgrade. Image NPMs are now stored in the ImagePackage type
    """

    __tablename__ = "image_npms"

    image_user_id = Column(String, primary_key=True)
    image_id = Column(String, primary_key=True)
    path_hash = Column(String, primary_key=True)  # The sha256 hash of the path in hex
    path = Column(String)
    name = Column(String)
    origins_json = Column(StringJSON)
    source_pkg = Column(String)
    licenses_json = Column(StringJSON)
    versions_json = Column(StringJSON)
    latest = Column(String)
    seq_id = Column(
        Integer, NPM_SEQ, server_default=NPM_SEQ.next_value()
    )  # Note this is not autoincrement as the upgrade code in upgrade.py sets. This table is no longer used as of 0.3.1 and is here for upgrade continuity only.

    image = relationship("Image", back_populates="npms")

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        Index("idx_npm_seq", seq_id),
        {},
    )

    def __repr__(self):
        return "<{} user_id={}, img_id={}, name={}>".format(
            self.__class__, self.image_user_id, self.image_id, self.name
        )


GEM_SEQ = Sequence("image_gems_seq_id_seq", metadata=Base.metadata)


class ImageGem(Base):
    """
    NOTE: This is a deprecated class used for legacy support. Gems are now loaded as types of packages for the ImagePackage class
    """

    __tablename__ = "image_gems"

    image_user_id = Column(String, primary_key=True)
    image_id = Column(String, primary_key=True)
    path_hash = Column(String, primary_key=True)  # The sha256 hash of the path in hex
    path = Column(String)
    name = Column(String)
    files_json = Column(StringJSON)
    origins_json = Column(StringJSON)
    source_pkg = Column(String)
    licenses_json = Column(StringJSON)
    versions_json = Column(StringJSON)
    latest = Column(String)
    seq_id = Column(
        Integer, GEM_SEQ, server_default=GEM_SEQ.next_value()
    )  # This table is no longer used as of 0.3.1 and is here for upgrade continuity only.

    image = relationship("Image", back_populates="gems")

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        Index("idx_gem_seq", seq_id),
        {},
    )

    def __repr__(self):
        return "<{} user_id={}, img_id={}, name={}>".format(
            self.__class__, self.image_user_id, self.image_id, self.name
        )


class ImageCpe(Base):
    __tablename__ = "image_cpes"

    image_user_id = Column(String, primary_key=True)
    image_id = Column(String, primary_key=True)
    pkg_type = Column(String, primary_key=True)  # java, python, gem, npm, etc

    pkg_path = Column(String, primary_key=True)
    cpetype = Column(String, primary_key=True)
    vendor = Column(String, primary_key=True)
    name = Column(String, primary_key=True)
    version = Column(String, primary_key=True)
    update = Column(String, primary_key=True)
    meta = Column(String, primary_key=True)

    image = relationship("Image", back_populates="cpes")

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        Index(truncate_index_name("ix_image_cpe_user_img"), image_id, image_user_id),
        {},
    )

    def __repr__(self):
        return (
            "<{} user_id={}, img_id={}, name={}, version={}, type={}, path={}>".format(
                self.__class__,
                self.image_user_id,
                self.image_id,
                self.name,
                self.version,
                self.pkg_type,
                self.pkg_path,
            )
        )

    def fixed_in(self):
        return None

    def get_cpestring(self):
        ret = None
        try:
            final_cpe = ["cpe", "-", "-", "-", "-", "-", "-"]
            final_cpe[1] = self.cpetype
            final_cpe[2] = self.vendor
            final_cpe[3] = self.name
            final_cpe[4] = self.version
            final_cpe[5] = self.update
            final_cpe[6] = self.meta
            ret = ":".join(final_cpe)
        except:
            ret = None

        return ret

    def get_cpe23string(self):
        ret = None
        try:
            final_cpe = [
                "cpe",
                "2.3",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
                "-",
            ]
            final_cpe[2] = self.cpetype
            final_cpe[3] = self.vendor
            final_cpe[4] = self.name
            final_cpe[5] = self.version
            final_cpe[6] = self.update
            final_cpe[7] = self.meta
            # final_cpe[8] = self.language
            # final_cpe[9] = self.sw_edition
            # final_cpe[10] = self.target_sw
            # final_cpe[11] = self.target_hw
            # final_cpe[12] = self.other
            ret = ":".join(final_cpe)
        except:
            ret = None
        return ret

    def get_cpe23_fs_for_sbom(self):
        """
        Returns the formatted string representation of 2.3 CPE for use in sbom constructed for Grype

        A 2.3 CPE is in the format
        cpe:2.3:part:vendor:product:version:update:edition:language:sw_edition:target_sw:target_hw:other

        The value '-' for a CPE component means the field is not applicable. Component comparison results in not-equal
        if one CPE has the component set (to value other than * or -) and another CPE indicates the same component is not applicable (-)
        Grype uses all the CPE components for finding a match against the CPEs provided by the vulnerability data.
        Anchore engine does not currently record the last 5 components and thereby defaults them to '-'.
        But that runs the risk of missed matches because of Grype's matching logic as explained above.
        This function is at the other end of the spectrum where it defaults all missing components to the wild character.
        While more matches are found this way, this approach runs the risk of finding false positives.
        Considering the components in play here, there may be a very small chance of such false positives since not many CPEs make use of them
        """
        cpe_components = [
            "cpe",
            "2.3",
            "-",  # part
            "-",  # vendor
            "-",  # product
            "-",  # version
            "-",  # update
            "-",  # edition
            # '*' for all components currently unknown to engine to enable matching in grype.
            "*",  # language
            "*",  # sw_edition
            "*",  # target_sw
            "*",  # target_hw
            "*",  # other
        ]
        cpe_components[2] = self.cpetype
        cpe_components[3] = self.vendor
        cpe_components[4] = self.name
        cpe_components[5] = self.version
        cpe_components[6] = self.update
        cpe_components[7] = self.meta

        return ":".join(cpe_components)


class FilesystemAnalysis(Base):
    """
    A unified and compressed record of the filesystem-level entries in an image. An alternative to the FilesystemItem approach,
    this allows much faster index operations due to a smaller index, but no queries into the content of the filesystems themselves.
    """

    __tablename__ = "image_fs_analysis_dump"

    compression_level = 6
    supported_algorithms = ["gzip"]

    image_id = Column(String, primary_key=True)
    image_user_id = Column(String, primary_key=True)

    compressed_content_hash = Column(String)
    compressed_file_json = Column(LargeBinary, nullable=False)
    total_entry_count = Column(Integer, default=0)
    file_count = Column(Integer, default=0)
    directory_count = Column(Integer, default=0)
    non_packaged_count = Column(Integer, default=0)
    suid_count = Column(Integer, default=0)
    image = relationship("Image", back_populates="fs")
    compression_algorithm = Column(String, default="gzip")
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(
        DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow
    )

    image = relationship("Image", back_populates="fs")

    _files = None

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        {},
    )

    # NOTE: operations on the content of the dict itself will not trigger dirty updates and a flush to db,
    # must explicitly set the value to a new dict if writes need to be persisted.

    @property
    def files(self):
        if not self._files:
            self._files = self._files_json()
        return self._files

    @files.setter
    def files(self, value):
        self._files = value
        self._files_from_json(self._files)

    def _files_json(self):
        if self.compression_algorithm == "gzip":
            return json.loads(
                ensure_str(zlib.decompress(ensure_bytes(self.compressed_file_json)))
            )
        else:
            raise ValueError(
                "Got unexpected compresssion algorithm value: {}. Expected {}".format(
                    self.compression_algorithm, self.supported_algorithms
                )
            )

    def _files_from_json(self, file_json):
        """
        Compress and hash the file_json content
        :param file_json:
        :return:
        """
        self.compressed_file_json = zlib.compress(json.dumps(file_json).encode("utf-8"))
        self.compression_algorithm = "gzip"
        self.compressed_content_hash = hashlib.sha256(
            self.compressed_file_json
        ).hexdigest()


class AnalysisArtifact(Base):
    """
    A generic container for an analysis result that doesn't require significant structure.
    Basically wraps a key-value output from a specific analyzer.

    """

    __tablename__ = "image_analysis_artifacts"

    image_id = Column(String, primary_key=True)
    image_user_id = Column(String, primary_key=True)
    analyzer_id = Column(
        String, primary_key=True
    )  # The name of the analyzer (e.g. layer_info)
    analyzer_artifact = Column(
        String, primary_key=True
    )  # The analyzer artifact name (e.g. layers_to_dockerfile)
    analyzer_type = Column(
        String, primary_key=True
    )  # The analyzer type (e.g. base, user, or extra)
    artifact_key = Column(String, primary_key=True)
    str_value = Column(Text)
    json_value = Column(StringJSON)
    binary_value = Column(LargeBinary)
    created_at = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )
    last_modified = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )

    image = relationship("Image", back_populates="analysis_artifacts")

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(image_id, image_user_id),
            refcolumns=("images.id", "images.user_id"),
        ),
        {},
    )


class Image(Base):
    """
    The core image analysis record. Contains metadata about the image itself.

    """

    __tablename__ = "images"

    id = Column(String, primary_key=True)
    user_id = Column(
        String, primary_key=True
    )  # Images are namespaced in the system to prevent overlap

    state = Column(
        Enum("failed", "initializing", "analyzing", "analyzed", name="image_states"),
        default="initializing",
    )  # For now we only load analyzed images, no progress tracking
    anchore_type = Column(
        Enum(
            "undefined",
            "base",
            "application",
            "user",
            "intermediate",
            name="anchore_image_types",
        ),
        default="undefined",
    )  # TODO: verify if base or undefined should be default

    size = Column(BigInteger)
    created_at = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )
    last_modified = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )

    digest = Column(String)
    distro_name = Column(String)
    distro_version = Column(String)
    like_distro = Column(String)

    # Should be native JSON, can be handled
    layers_json = Column(StringJSON)
    docker_history_json = Column(StringJSON)
    docker_data_json = Column(StringJSON)
    familytree_json = Column(StringJSON)
    layer_info_json = Column(StringJSON)
    dockerfile_contents = Column(Text)
    dockerfile_mode = Column(String, default="Guessed")

    packages = relationship(
        "ImagePackage",
        back_populates="image",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )
    fs = relationship(
        "FilesystemAnalysis",
        uselist=False,
        lazy="select",
        cascade=["all", "delete", "delete-orphan"],
    )

    # TODO - move these to ImagePackage records instead of individual tables
    gems = relationship(
        "ImageGem",
        back_populates="image",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )
    npms = relationship(
        "ImageNpm",
        back_populates="image",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )

    cpes = relationship(
        "ImageCpe",
        back_populates="image",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )
    analysis_artifacts = relationship(
        "AnalysisArtifact",
        back_populates="image",
        lazy="dynamic",
        cascade=["all", "delete", "delete-orphan"],
    )

    @property
    def distro_namespace(self):
        if self.distro_name and self.distro_version:
            return self.distro_name + ":" + self.distro_version
        else:
            return None

    def distro_namespace_obj(self):
        return DistroNamespace.for_obj(self)

    def get_packages_by_type(self, pkg_type):
        db = get_thread_scoped_session()
        typed_packages = (
            db.query(ImagePackage)
            .filter(
                ImagePackage.image_id == self.id,
                ImagePackage.image_user_id == self.user_id,
                ImagePackage.pkg_type == pkg_type,
            )
            .all()
        )
        return typed_packages

    def vulnerabilities(self):
        """
        Load vulnerabilties for all packages in this image
        :return: list of ImagePackageVulnerabilities
        """
        db = get_thread_scoped_session()
        known_vulnerabilities = (
            db.query(ImagePackageVulnerability)
            .filter(
                ImagePackageVulnerability.pkg_user_id == self.user_id,
                ImagePackageVulnerability.pkg_image_id == self.id,
            )
            .all()
        )
        return known_vulnerabilities

    def cpe_vulnerabilities(self, _nvd_cls, _cpe_cls):
        """
        Similar to the vulnerabilities function, but using the cpe matches instead, basically the NVD raw data source

        :return: list of (image_cpe, cpe_vulnerability) tuples
        """
        db = get_thread_scoped_session()
        if not _nvd_cls or not _cpe_cls:
            _nvd_cls, _cpe_cls = select_nvd_classes(db)
        cpe_vulnerabilities = (
            db.query(ImageCpe, _cpe_cls)
            .filter(
                ImageCpe.image_id == self.id,
                ImageCpe.image_user_id == self.user_id,
                func.lower(ImageCpe.name) == _cpe_cls.name,
                ImageCpe.version == _cpe_cls.version,
            )
            .options(joinedload(_cpe_cls.parent, innerjoin=True))
            .all()
        )

        # vulndb is similar to nvd cpes, add them here
        cpe_vulnerabilities.extend(
            db.query(ImageCpe, VulnDBCpe)
            .filter(
                ImageCpe.image_id == self.id,
                ImageCpe.image_user_id == self.user_id,
                func.lower(ImageCpe.name) == VulnDBCpe.name,
                ImageCpe.version == VulnDBCpe.version,
                VulnDBCpe.is_affected.is_(True),
            )
            .options(joinedload(VulnDBCpe.parent, innerjoin=True))
            .all()
        )

        return cpe_vulnerabilities

    def get_image_base(self):
        """
        Get the image that is this image's base image if it exists. Indicated by first entry of the familytree
        :return: Image object
        """
        base_id = self.familytree_json[0] if self.familytree_json else self
        if base_id == self.id:
            return self
        else:
            db = get_thread_scoped_session()
            return db.query(Image).get((base_id, self.user_id))

    def __repr__(self):
        return "<Image user_id={}, id={}, distro={}, distro_version={}, created_at={}, last_modified={}>".format(
            self.user_id,
            self.id,
            self.distro_name,
            self.distro_version,
            self.created_at,
            self.last_modified,
        )


class ImagePackageVulnerability(Base):
    """
    Provides a mapping between ImagePackage and Vulnerabilities
    """

    __tablename__ = "image_package_vulnerabilities"

    pkg_user_id = Column(String, primary_key=True)
    pkg_image_id = Column(String, primary_key=True)
    pkg_name = Column(String, primary_key=True)
    pkg_version = Column(String, primary_key=True)
    pkg_type = Column(String, primary_key=True)  # RHEL, DEB, APK, etc.
    pkg_arch = Column(String, default="N/A", primary_key=True)

    pkg_path = Column(String, default="pkgdb", primary_key=True)

    vulnerability_id = Column(String, primary_key=True)
    vulnerability_namespace_name = Column(String)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    package = relationship("ImagePackage", back_populates="vulnerabilities")
    vulnerability = relationship("Vulnerability")

    __table_args__ = (
        ForeignKeyConstraint(
            columns=(
                pkg_image_id,
                pkg_user_id,
                pkg_name,
                pkg_version,
                pkg_type,
                pkg_arch,
                pkg_path,
            ),
            refcolumns=(
                ImagePackage.image_id,
                ImagePackage.image_user_id,
                ImagePackage.name,
                ImagePackage.version,
                ImagePackage.pkg_type,
                ImagePackage.arch,
                ImagePackage.pkg_path,
            ),
        ),
        ForeignKeyConstraint(
            columns=(vulnerability_id, vulnerability_namespace_name),
            refcolumns=(Vulnerability.id, Vulnerability.namespace_name),
        ),
        {},
    )

    def fix_candidates(self) -> list:
        """
        Return the list of FixedArtifact record given a package has been matched to the vulnerability by package/src-package
        name. Does not perform a version check. Will return empty list if no matches

        :return: the name-matched FixedArtifact record list

        """

        if self.vulnerability.fixed_in:
            name_matches = [self.pkg_name, self.package.normalized_src_pkg]
            return [x for x in self.vulnerability.fixed_in if x.name in name_matches]
        return []

    def fixed_artifact(self):
        """
        Return the FixedArtifact record given a package has been matched to the vulnerability

        :return: the matched FixedArtifact record or None if not found
        """

        candidates = self.fix_candidates()
        candidate_count = len(candidates) if candidates else 0
        if candidate_count == 0:
            return None
        elif candidate_count == 1:
            return candidates[0]

        fixed_in = None

        # candidate_count >= 1
        # Do version checks. This package must be affected by the range but not fixed.
        matched = [x for x in candidates if x.match_but_not_fixed(self.package)]

        if len(matched) == 1:
            fixed_in = matched[0]
        elif len(matched) > 1:
            matched.sort(key=lambda x: x.updated_at, reverse=True)
            fixed_in = matched[0]
            # This shouldn't happen since it means there isn't consistency in the data

        return fixed_in

    def fixed_in(self, fixed_in: FixedArtifact = None):
        """
        Return the fixed_in version string value given a package matched (in case there are multiple packages specified in the vuln.

        :return: the fixed in version string if any or None if not found
        """
        if not fixed_in:
            fixed_in = self.fixed_artifact()
        fix_available_in = (
            fixed_in.version if fixed_in and fixed_in.version != "None" else None
        )

        # NOTE: semver version format indicates a range where package
        # is vulnerable (as opposed to a value where anythng < value
        # is vulnerable, and the fix itself is known to exist), so we prepend a 'not' to indicate 'fix is available, if not in semver range'
        if fixed_in and fixed_in.version_format in ["semver"]:
            # Github Advisories can add the real version where there is a fix if any.
            metadata = fixed_in.fix_metadata or {}
            first_patched_version = metadata.get("first_patched_version")
            if first_patched_version:
                return first_patched_version

            if (
                fix_available_in
                and fixed_in.fix_metadata
                and fixed_in.fix_metadata.get("fix_exists", False)
            ):
                fix_available_in = "! {}".format(fix_available_in)
            else:
                fix_available_in = None

        return fix_available_in

    def fix_has_no_advisory(self, fixed_in: FixedArtifact = None):
        """
        For a given package vulnerability match, if the issue won't be addressed by the vendor return True.
        Return False otherwise
        :return:
        """
        if not fixed_in:
            fixed_in = self.fixed_artifact()

        return (
            fixed_in.vendor_no_advisory
            if fixed_in and isinstance(fixed_in, FixedArtifact)
            else False
        )

    @classmethod
    def from_pair(cls, package_obj, vuln_obj):
        rec = ImagePackageVulnerability()
        rec.pkg_name = package_obj.name
        rec.pkg_type = package_obj.pkg_type
        rec.pkg_arch = package_obj.arch
        rec.pkg_image_id = package_obj.image_id
        rec.pkg_user_id = package_obj.image_user_id
        rec.pkg_version = package_obj.version
        rec.pkg_path = package_obj.pkg_path
        rec.vulnerability_id = (
            vuln_obj.vulnerability_id
            if hasattr(vuln_obj, "vulnerability_id")
            else vuln_obj.id
        )
        rec.vulnerability_namespace_name = vuln_obj.namespace_name
        return rec

    def __repr__(self):
        return "<ImagePackageVulnerability img_user_id={}, img_id={}, pkg_name={}, pkg_version={}, vuln_id={}, vuln_namespace={}, pkg_path={}>".format(
            self.pkg_user_id,
            self.pkg_image_id,
            self.pkg_name,
            self.pkg_version,
            self.vulnerability_id,
            self.vulnerability_namespace_name,
            self.pkg_path,
        )

    # To support hash functions like set operations, ensure these align with primary key comparisons to ensure two identical records would match as such.
    def __eq__(self, other):
        return isinstance(other, type(self)) and (
            self.pkg_user_id,
            self.pkg_image_id,
            self.pkg_name,
            self.pkg_version,
            self.pkg_type,
            self.pkg_arch,
            self.vulnerability_id,
            self.pkg_path,
        ) == (
            other.pkg_user_id,
            other.pkg_image_id,
            other.pkg_name,
            other.pkg_version,
            other.pkg_type,
            other.pkg_arch,
            other.vulnerability_id,
            other.pkg_path,
        )

    def __hash__(self):
        return hash(
            (
                self.pkg_user_id,
                self.pkg_image_id,
                self.pkg_name,
                self.pkg_version,
                self.pkg_type,
                self.pkg_arch,
                self.vulnerability_id,
                self.pkg_path,
            )
        )


class IDistroMapper(object):
    """
    Interface for a distro mapper object
    """

    def __init__(self, distro, version, like_distro, found_mapping):
        self.from_distro = distro
        self.from_version = version
        self.from_like_distro = like_distro
        self.found_mapping = found_mapping
        self.mapping = self._do_mapping()

    def _do_mapping(self):
        """
        Map from the given values to a new distro if an explicit mapping exists or else None

        :param distro_name:
        :param distro_version:
        :param like_distro:
        :return: list of tuples: [(distro, version, flavor), ... ,(distro, versionN, flavorN)]
        """
        pass

    def _map_name(self, distro_name, distro_version, like_distro, found_mapping=None):
        pass

    def _map_version(
        self, distro_name, distro_version, like_distro, found_mapping=None
    ):
        pass

    def _map_flavor(self, distro_name, distro_version, like_distro, found_mapping=None):
        pass


class VersionPreservingDistroMapper(IDistroMapper):
    def _do_mapping(self):
        """
        Map from the given values to a new distro if an explicit mapping exists or else None

        :param distro_name:
        :param distro_version:
        :param like_distro:
        :return: list of tuples: [(distro, version, flavor), ... ,(distro, versionN, flavorN)]
        """

        distro = None
        versions = None
        flavor = None

        try:
            distro = self._map_name(
                self.from_distro,
                self.from_version,
                self.from_like_distro,
                self.found_mapping,
            )
            flavor = self._map_flavor(
                self.from_distro,
                self.from_version,
                self.from_like_distro,
                self.found_mapping,
            )
            versions = self._map_version(
                self.from_distro,
                self.from_version,
                self.from_like_distro,
                self.found_mapping,
            )
            return [
                DistroTuple(distro=distro, version=v, flavor=flavor) for v in versions
            ]
        except:
            log.exception(
                "Failed to fully construct the mapped distro from: {}, {}, {}".format(
                    self.from_distro, self.from_version, self.from_like_distro
                )
            )
            raise

    def _map_name(self, distro_name, distro_version, like_distro, found_mapping=None):
        if found_mapping:
            return found_mapping.to_distro
        else:
            return distro_name

    def _map_flavor(self, distro_name, distro_version, like_distro, found_mapping=None):
        if found_mapping:
            return found_mapping.flavor
        else:
            db = get_thread_scoped_session()
            candidates = like_distro.split(",") if like_distro else []
            for c in candidates:
                mapping = db.query(DistroMapping).get(c)
                if mapping:
                    return mapping.flavor
            return None

    def _map_version(
        self, distro_name, distro_version, like_distro, found_mapping=None
    ):
        """
        Maps version into a list of versions ordered by closeness of match: [full original version, major.minor]
        Only provides the second, major/minor, mapping if the version matches a dot delimited digit sequence

        :param distro_name:
        :param distro_version:
        :param like_distro:
        :param found_mapping:
        :return:
        """

        # Parse down to major, minor only if has a subminor
        patt = re.match(r"(\d+)\.(\d+)\.(\d+)", distro_version)
        if patt:
            major, minor, subminor = patt.groups()
            return [distro_version, "{}.{}".format(major, minor), "{}".format(major)]

        # Parse dow to only major
        patt = re.match(r"(\d+)\.(\d+)", distro_version)
        if patt:
            major, minor = patt.groups()
            return [distro_version, "{}.{}".format(major, minor), "{}".format(major)]

        return [distro_version]


class DistroMapping(Base):
    """
    A mapping entry between a distro with known cve feed and other similar distros.
    Used to explicitly map similar distros to a base feed for cve matches.
    """

    __tablename__ = "distro_mappings"
    __distro_mapper_cls__ = VersionPreservingDistroMapper

    from_distro = Column(String, primary_key=True)  # The distro to be checked
    to_distro = Column(
        String
    )  # The distro to use instead of the pk distro to do cve checks
    flavor = Column(String)  # The distro flavor to use (e.g. RHEL, or DEB)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    @classmethod
    def distros_for(cls, distro, version, like_distro=""):
        """
        Returns a pair of mappings for the given object assuming the object has distro_name and distro_version and like_distro
        fields. First element of the pair is the exact distro namespace, the second is either None or a like-relation mapping
         using the to_distro value of the found mapping.

        :param obj:
        :return: list of DistroTuples for most-to-least exact match
        """
        db = get_thread_scoped_session()
        found = db.query(DistroMapping).get(distro)
        mapper = cls.__distro_mapper_cls__(distro, version, like_distro, found)
        return mapper.mapping

    @classmethod
    def distros_mapped_to(cls, distro, version):
        """
        Reverse of distros_for, returns the list of namespace names that would map to the given distro and version.

        :param distro:
        :param version:
        :return:
        """
        db = get_thread_scoped_session()
        name_matches = (
            db.query(DistroMapping).filter(DistroMapping.to_distro == distro).all()
        )
        return [
            DistroTuple(
                distro=mapping.from_distro, version=version, flavor=mapping.flavor
            )
            for mapping in name_matches
        ]

    def __str__(self):
        return "<DistroMapping>from={} to={}, flavor={}".format(
            self.from_distro, self.to_distro, self.flavor
        )


class DistroNamespace(object):
    """
    A helper object for holding and converting distro names and namespaces between image and vulnerability records.
    Abstracts the conversion of name, version, like_version sets into usable strings for comparing and matching records.

    The 'like' relation defines similar pkg types and cve feed sources. If distro A is like distro B, then distro A should be able to match
    against distro B's vulnerability feeds.

    """

    @classmethod
    def for_obj(cls, obj):
        if hasattr(obj, "distro_name") and hasattr(obj, "distro_version"):
            return DistroNamespace(
                getattr(obj, "distro_name"),
                getattr(obj, "distro_version"),
                like_distro=getattr(obj, "like_distro", None),
            )
        else:
            raise TypeError(
                "Given object must have attributes: distro_name, distro_version"
            )

    def __init__(self, name="UNKNOWN", version="UNKNOWN", like_distro=None):
        self.name = name
        self.version = version
        self.like_distro = like_distro
        self.mapping = DistroMapping.distros_for(
            self.name, self.version, self.like_distro
        )
        self.flavor = self.mapping[0].flavor if self.mapping else "Unknown"
        self.namespace_name = DistroNamespace.as_namespace_name(
            self.mapping[0].distro, self.mapping[0].version
        )
        self.like_namespace_names = [
            DistroNamespace.as_namespace_name(x.distro, x.version) for x in self.mapping
        ]

    @staticmethod
    def as_namespace_name(name, version):
        """
        Direct conversion to a single namespace name. Does not follow any 'like' relations.

        :return:
        """
        return name + ":" + version

    def mapped_names(self):
        """
        Return the list of namespaces that can map to this one. Only returns distro names who's DistroMapping relation is the exact
        name of this object's name field. Ensures that only direct mappings are returned, and avoids intermediate names being mapped
        as related to each other when they simply share a parent.

        E.g.

        ol like centos,
        fedora like centos,
        fedora not like ol
        ol not like fedora

        :return: list of name, version pairs
        """
        return [
            x.distro for x in DistroMapping.distros_mapped_to(self.name, self.version)
        ]


class StorageInterface(object):
    """
    Interface for a stored object in policy engine stored in persistence. Expects a member/column called result in the implementation
    """

    def _constuct_raw_result(self, result_json):
        return {"type": "direct", "result": result_json}

    def _construct_remote_result(self, bucket, key, digest):
        """
        Build the result json for the db record

        :param bucket: bucket in archive to lookup result
        :param key: key in archive to lookup result
        :param digest: sha256 digest of the result
        :return:
        """
        return {
            "type": "archive",
            "digest": digest,
            "uri": "catalog://{bucket}/{key}".format(bucket=bucket, key=key),
        }

    def add_raw_result(self, result_json):
        self.result = self._constuct_raw_result(result_json)

    def add_remote_result(self, bucket, key, result_digest):
        self.result = self._construct_remote_result(bucket, key, result_digest)

    def is_raw(self):
        return self.result["type"] == "direct"

    def is_archive_ref(self):
        return self.result["type"] == "archive"

    def archive_tuple(self):
        """
        Returns the bucket, key tuple for an archive reference
        :return:
        """

        if self.is_archive_ref():
            uri = self.result.get("uri", "")
            _, path = uri.split("catalog://", 1)
            bucket, key = path.split("/", 1)
            return bucket, key
        else:
            raise ValueError("Result type is not an archive")


class ImageVulnerabilitiesReport(Base, StorageInterface):
    __tablename__ = "image_vulnerabilities_reports"

    account_id = Column(String, primary_key=True)
    image_digest = Column(String, primary_key=True)
    report_key = Column(String)
    generated_at = Column(DateTime)
    result = Column(JSONB)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    last_modified = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
    )

    __table_args__ = (
        Index(
            truncate_index_name("ix_image_vulnerabilities_reports_report_key"),
            report_key,
        ),
        Index(
            truncate_index_name("ix_image_vulnerabilities_reports_generated_at"),
            generated_at,
        ),
    )


class CachedPolicyEvaluation(Base, StorageInterface):
    __tablename__ = "policy_engine_evaluation_cache"

    user_id = Column(String, primary_key=True)
    image_id = Column(String, primary_key=True)
    eval_tag = Column(String, primary_key=True)
    bundle_id = Column(
        String, primary_key=True
    )  # Need both id and digest to differentiate a new bundle vs update to bundle that requires a flush of the old record
    bundle_digest = Column(String, primary_key=True)

    result = Column(
        StringJSON, nullable=False
    )  # Result struct, based on the 'type' inside, may be literal content or a reference to the archive

    created_at = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )
    last_modified = Column(
        DateTime,
        default=datetime.datetime.utcnow,
        onupdate=datetime.datetime.utcnow,
        nullable=False,
    )

    def key_tuple(self):
        return (
            self.user_id,
            self.image_id,
            self.eval_tag,
            self.bundle_id,
            self.bundle_digest,
        )


def select_nvd_classes(db=None):
    if not db:
        db = get_thread_scoped_session()

    _nvd_cls = NvdMetadata
    _cpe_cls = CpeVulnerability
    try:
        fmd = db.query(FeedMetadata).filter(FeedMetadata.name == "nvdv2").first()
        if fmd and fmd.last_full_sync:
            _nvd_cls = NvdV2Metadata
            _cpe_cls = CpeV2Vulnerability
    except Exception as err:
        log.warn("could not query for nvdv2 sync: {}".format(err))

    log.debug("selected {}/{} nvd classes".format(_nvd_cls, _cpe_cls))
    return _nvd_cls, _cpe_cls


def _get_one_or_none(key, val, collection):
    """
    Find a match for the object in the collection using the key-value pair,
    return the result only if 1 match is found.

    Example instance of collection
    [
        {
          "source": "CVE ID",
          "url": "http://cve.mitre.org/cgi-bin/cvename.cgi?name=2000-0089"
        },
        {
          "source": "Bugtraq ID",
          "url": "http://www.securityfocus.com/bid/947"
        },
    ]
    """
    if not key or not val:
        return None
    result = None

    for entry in collection:
        if entry.get(key) == val:
            if result:
                return None
            else:
                result = entry
    return result
