CREATE TABLE vulnerability_description_translations (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    source_description_id BIGINT NOT NULL REFERENCES vulnerability_descriptions (id) ON DELETE CASCADE,
    vulnerability_id BIGINT NOT NULL REFERENCES vulnerabilities (id) ON DELETE CASCADE,
    source TEXT NOT NULL,
    source_identifier TEXT NOT NULL,
    source_language TEXT NOT NULL,
    target_language TEXT NOT NULL,
    provider TEXT NOT NULL,
    model TEXT NOT NULL,
    source_text_hash TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    text TEXT,
    error_text TEXT,
    translated_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (source, source_identifier, source_text_hash, target_language)
);

CREATE INDEX vulnerability_description_translations_lookup_idx
    ON vulnerability_description_translations (vulnerability_id, target_language);

CREATE INDEX vulnerability_description_translations_status_idx
    ON vulnerability_description_translations (status, updated_at);

CREATE INDEX vulnerability_descriptions_source_language_idx
    ON vulnerability_descriptions (source, language);
