CREATE TABLE result_inventories (
    attempt_id TEXT PRIMARY KEY,
    state TEXT NOT NULL DEFAULT 'open' CHECK (state IN ('open', 'closed', 'sealed')),
    manifest_schema_version INTEGER,
    manifest_id TEXT,
    manifest_store TEXT,
    manifest_bucket TEXT,
    manifest_key TEXT,
    manifest_version_id TEXT,
    manifest_sha256 TEXT,
    manifest_size BIGINT,
    member_count BIGINT NOT NULL DEFAULT 0 CHECK (member_count >= 0),
    total_bytes BIGINT NOT NULL DEFAULT 0 CHECK (total_bytes >= 0),
    CHECK (
        (state <> 'sealed' AND manifest_schema_version IS NULL AND manifest_id IS NULL
            AND manifest_store IS NULL AND manifest_bucket IS NULL AND manifest_key IS NULL
            AND manifest_version_id IS NULL AND manifest_sha256 IS NULL AND manifest_size IS NULL)
        OR
        (state = 'sealed' AND manifest_schema_version IS NOT NULL AND manifest_id IS NOT NULL
            AND manifest_store IS NOT NULL AND manifest_bucket IS NOT NULL AND manifest_key IS NOT NULL
            AND manifest_sha256 IS NOT NULL AND manifest_size IS NOT NULL)
    )
);

CREATE TABLE result_inventory_members (
    attempt_id TEXT NOT NULL REFERENCES result_inventories(attempt_id) ON DELETE CASCADE,
    member_id TEXT NOT NULL,
    path TEXT NOT NULL,
    size BIGINT NOT NULL CHECK (size >= 0),
    object_store TEXT NOT NULL,
    object_bucket TEXT NOT NULL,
    object_key TEXT NOT NULL,
    object_version_id TEXT,
    object_sha256 TEXT NOT NULL,
    PRIMARY KEY (attempt_id, member_id),
    UNIQUE (attempt_id, path)
);

CREATE INDEX result_inventory_members_order_idx
    ON result_inventory_members (attempt_id, path, member_id);