ALTER TABLE jobs
    ADD COLUMN retry_not_before TIMESTAMPTZ,
    ADD COLUMN coordination_cleaned_at TIMESTAMPTZ,
    ADD COLUMN recovery_checked_at TIMESTAMPTZ;

ALTER TABLE attempts
    DROP CONSTRAINT attempts_state_timestamps_check,
    ADD COLUMN worker_down_at TIMESTAMPTZ,
    ADD CONSTRAINT attempts_state_timestamps_check CHECK (
        (state = 'authorized' AND executing_at IS NULL AND reaped_at IS NULL
            AND stopped_at IS NULL AND committed_at IS NULL)
        OR (state = 'executing' AND executing_at IS NOT NULL AND reaped_at IS NULL
            AND stopped_at IS NULL AND committed_at IS NULL)
        OR (state = 'reaped' AND executing_at IS NULL AND reaped_at IS NOT NULL
            AND stopped_at IS NULL AND committed_at IS NULL)
        OR (state = 'stopped' AND reaped_at IS NULL
            AND stopped_at IS NOT NULL AND committed_at IS NULL)
        OR (state = 'committed' AND executing_at IS NOT NULL AND reaped_at IS NULL
            AND stopped_at IS NULL AND committed_at IS NOT NULL)
    );

CREATE TABLE attempt_staging_cleanup (
    cleanup_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    attempt_id TEXT NOT NULL REFERENCES attempts (attempt_id),
    kind TEXT NOT NULL CHECK (kind IN ('object', 'multipart')),
    target_store TEXT NOT NULL,
    bucket TEXT NOT NULL,
    object_key TEXT NOT NULL,
    version_id TEXT,
    upload_id TEXT,
    state TEXT NOT NULL DEFAULT 'pending'
        CHECK (state IN ('pending', 'claimed', 'complete')),
    claim_token TEXT,
    claimed_until TIMESTAMPTZ,
    failure_count BIGINT NOT NULL DEFAULT 0 CHECK (failure_count >= 0),
    last_error TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    CONSTRAINT attempt_staging_cleanup_identity_shape_check CHECK (
        (kind = 'object' AND upload_id IS NULL)
        OR (kind = 'multipart' AND version_id IS NULL AND upload_id IS NOT NULL)
    ),
    CONSTRAINT attempt_staging_cleanup_claim_check CHECK (
        (state = 'pending' AND claim_token IS NULL AND claimed_until IS NULL
            AND completed_at IS NULL)
        OR (state = 'claimed' AND claim_token IS NOT NULL AND claimed_until IS NOT NULL
            AND completed_at IS NULL)
        OR (state = 'complete' AND claim_token IS NULL AND claimed_until IS NULL
            AND completed_at IS NOT NULL)
    ),
    UNIQUE NULLS NOT DISTINCT (
        attempt_id, kind, target_store, bucket, object_key, version_id, upload_id
    )
);

CREATE INDEX attempt_staging_cleanup_claimable_idx
    ON attempt_staging_cleanup (cleanup_id)
    WHERE state IN ('pending', 'claimed');

CREATE INDEX jobs_recovery_idx
    ON jobs (updated_at, job_id)
    WHERE phase NOT IN ('succeeded', 'failed', 'cancelled')
       OR coordination_cleaned_at IS NULL;