CREATE TABLE nvd_cpe_matches (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vulnerability_id BIGINT NOT NULL REFERENCES vulnerabilities (id) ON DELETE CASCADE,
    cve_id TEXT NOT NULL,
    match_criteria_id TEXT,
    criteria TEXT NOT NULL,
    vulnerable BOOLEAN NOT NULL,
    cpe_version TEXT NOT NULL,
    part TEXT NOT NULL,
    vendor TEXT NOT NULL,
    product TEXT NOT NULL,
    version TEXT,
    version_start_including TEXT,
    version_start_excluding TEXT,
    version_end_including TEXT,
    version_end_excluding TEXT,
    criteria_data JSONB NOT NULL DEFAULT '{}'::jsonb,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX nvd_cpe_matches_lookup_idx
    ON nvd_cpe_matches (part, vendor, product)
    INCLUDE (
        vulnerability_id,
        criteria,
        version,
        version_start_including,
        version_start_excluding,
        version_end_including,
        version_end_excluding
    )
    WHERE vulnerable = true;

CREATE INDEX nvd_cpe_matches_vulnerability_id_idx
    ON nvd_cpe_matches (vulnerability_id);

CREATE INDEX nvd_cpe_matches_cve_id_idx
    ON nvd_cpe_matches (cve_id);
