CREATE TABLE buckets (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_buckets_name ON buckets (name);

CREATE TABLE objects (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bucket_id     UUID NOT NULL REFERENCES buckets(id) ON DELETE CASCADE,
    key           TEXT NOT NULL,
    size          BIGINT NOT NULL,
    etag          TEXT NOT NULL,
    content_type  TEXT NOT NULL DEFAULT 'application/octet-stream',
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX idx_objects_bucket_key ON objects (bucket_id, key);
CREATE INDEX idx_objects_key_prefix ON objects (bucket_id, key text_pattern_ops);

CREATE TABLE object_metadata (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    object_id   UUID NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    meta_key    TEXT NOT NULL,
    meta_value  TEXT NOT NULL
);
CREATE UNIQUE INDEX idx_metadata_object_key ON object_metadata (object_id, meta_key);
CREATE INDEX idx_metadata_object_id ON object_metadata (object_id);

CREATE TABLE blocks (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    object_id     UUID NOT NULL REFERENCES objects(id) ON DELETE CASCADE,
    block_index   INT NOT NULL,
    data          BYTEA NOT NULL,
    block_size    INT NOT NULL
);
CREATE UNIQUE INDEX idx_blocks_object_index ON blocks (object_id, block_index);
CREATE INDEX idx_blocks_object_id ON blocks (object_id);