CREATE TYPE batch_outcome_status AS ENUM (
    -- Extraction-time outcomes (set by batch worker)
    'submitted',
    'duplicate',
    'failed_open',
    'failed_read',
    'failed_s3_upload',
    'failed_upload',
    'invalid_type',
    -- Pipeline outcomes (set by runners, update the row in place)
    'init_complete',
    'init_duplicate',
    'sync_complete',
    'sync_skipped',
    'clean_passed',
    'clean_failed'
);

CREATE TABLE batch_document_outcomes (
    id uuid PRIMARY KEY DEFAULT uuid_generate_v7(),
    batch_id uuid NOT NULL,
    filename text NOT NULL,
    outcome batch_outcome_status NOT NULL,
    error_detail text,
    document_id uuid,
    updated_at timestamp NOT NULL DEFAULT NOW(),
    created_at timestamp NOT NULL DEFAULT NOW(),
    FOREIGN KEY (batch_id) REFERENCES batch_uploads(id),
    FOREIGN KEY (document_id) REFERENCES documents(id),
    UNIQUE (batch_id, filename)
);

CREATE INDEX idx_batch_doc_outcomes_batch_id ON batch_document_outcomes(batch_id);
CREATE INDEX idx_batch_doc_outcomes_document_id ON batch_document_outcomes(document_id);
CREATE INDEX idx_documents_batch_id_filename ON documents(batch_id, filename);