CREATE TABLE vulnerability_source_records (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source TEXT NOT NULL,
    identifier TEXT NOT NULL,
    title TEXT,
    description TEXT,
    severity TEXT,
    source_status TEXT,
    exploit_status TEXT,
    fix_status TEXT,
    vul_class TEXT,
    remediation TEXT,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    raw_data JSONB NOT NULL DEFAULT '{}'::jsonb,
    synced_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (source, identifier)
);

CREATE INDEX vulnerability_source_records_source_identifier_idx
    ON vulnerability_source_records (source, identifier);

CREATE INDEX vulnerability_source_records_metadata_gin_idx
    ON vulnerability_source_records USING GIN (metadata);

CREATE TABLE vulnerability_source_record_cves (
    source_record_id BIGINT NOT NULL REFERENCES vulnerability_source_records (id) ON DELETE CASCADE,
    cve_id TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (source_record_id, cve_id)
);

CREATE INDEX vulnerability_source_record_cves_cve_id_idx
    ON vulnerability_source_record_cves (cve_id);
