CREATE TABLE nvd_cpe_dictionary (
    cpe_name TEXT PRIMARY KEY,
    cpe_name_id TEXT NOT NULL,
    deprecated BOOLEAN NOT NULL,
    part TEXT NOT NULL,
    vendor TEXT NOT NULL,
    product TEXT NOT NULL,
    version TEXT,
    update TEXT,
    last_modified TIMESTAMPTZ,
    created TIMESTAMPTZ,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX nvd_cpe_dictionary_lookup_idx
    ON nvd_cpe_dictionary (part, vendor, product, version);

CREATE INDEX nvd_cpe_dictionary_cpe_name_id_idx
    ON nvd_cpe_dictionary (cpe_name_id);

CREATE TABLE nvd_cpe_aliases (
    source_cpe TEXT NOT NULL,
    target_cpe TEXT NOT NULL,
    source_cpe_name_id TEXT NOT NULL,
    target_cpe_name_id TEXT,
    source_part TEXT NOT NULL,
    source_vendor TEXT NOT NULL,
    source_product TEXT NOT NULL,
    source_version TEXT,
    source_update TEXT,
    target_part TEXT NOT NULL,
    target_vendor TEXT NOT NULL,
    target_product TEXT NOT NULL,
    target_version TEXT,
    target_update TEXT,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

ALTER TABLE nvd_cpe_aliases
    ADD CONSTRAINT nvd_cpe_aliases_pkey PRIMARY KEY (source_cpe, target_cpe);

CREATE INDEX nvd_cpe_aliases_source_lookup_idx
    ON nvd_cpe_aliases (source_part, source_vendor, source_product, source_version);
