CREATE TABLE deletion_operations (
    operation_id TEXT PRIMARY KEY,
    job_id TEXT NOT NULL UNIQUE REFERENCES jobs (job_id),
    state TEXT NOT NULL CHECK (
        state IN ('queued', 'running', 'retry_wait', 'blocked', 'completed')
    ),
    problem_class TEXT CHECK (
        problem_class IS NULL
        OR problem_class IN ('authorization', 'target_unavailable', 'configuration', 'internal')
    ),
    problem_message TEXT,
    created_at TIMESTAMPTZ NOT NULL,
    updated_at TIMESTAMPTZ NOT NULL,
    completed_at TIMESTAMPTZ,
    CONSTRAINT deletion_operations_problem_check CHECK (
        (state = 'blocked') = (problem_class IS NOT NULL AND problem_message IS NOT NULL)
    ),
    CONSTRAINT deletion_operations_completion_check CHECK (
        (state = 'completed') = (completed_at IS NOT NULL)
    )
);

ALTER TABLE jobs
    ADD COLUMN deletion_operation_id TEXT,
    ADD COLUMN hard_delete_started_at TIMESTAMPTZ,
    ADD CONSTRAINT jobs_deletion_operation_fk
        FOREIGN KEY (deletion_operation_id)
        REFERENCES deletion_operations (operation_id)
        DEFERRABLE INITIALLY DEFERRED,
    ADD CONSTRAINT jobs_deletion_lifecycle_check CHECK (
        (
            lifecycle IN ('visible', 'soft_deleted')
            AND deletion_operation_id IS NULL
            AND hard_delete_started_at IS NULL
        )
        OR (
            lifecycle IN ('hard_deleting', 'hard_deleted')
            AND deletion_operation_id IS NOT NULL
            AND hard_delete_started_at IS NOT NULL
        )
    );

CREATE TABLE deletion_items (
    item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    operation_id TEXT NOT NULL REFERENCES deletion_operations (operation_id),
    kind TEXT NOT NULL CHECK (kind IN ('write_intent', 'object', 'multipart', 'manifest')),
    priority SMALLINT NOT NULL CHECK (priority IN (0, 10, 100)),
    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),
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL,
    CONSTRAINT deletion_items_identity_shape_check CHECK (
        (kind IN ('write_intent', 'object', 'manifest') AND upload_id IS NULL)
        OR (kind = 'multipart' AND version_id IS NULL AND upload_id IS NOT NULL)
    ),
    CONSTRAINT deletion_items_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 (
        operation_id,
        kind,
        target_store,
        bucket,
        object_key,
        version_id,
        upload_id
    )
);

CREATE INDEX deletion_items_claimable_idx
    ON deletion_items (operation_id, priority, item_id)
    WHERE state IN ('pending', 'claimed');