-- V10__create_review_annotations.sql
CREATE TABLE review_annotations (
    id                UUID         PRIMARY KEY DEFAULT uuid_generate_v4(),
    review_id         UUID         NOT NULL,
    storage_key       VARCHAR(500) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    size_bytes        BIGINT       NOT NULL,
    sha256_hex        VARCHAR(64)  NOT NULL,
    uploaded_at       TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    CONSTRAINT fk_review_annotations_review FOREIGN KEY (review_id) REFERENCES reviews(id) ON DELETE CASCADE,
    CONSTRAINT uq_review_annotations_review UNIQUE (review_id)
);

CREATE INDEX idx_review_annotations_review ON review_annotations(review_id);