CREATE TABLE pools (
    pool_id        TEXT PRIMARY KEY,
    limit_bytes    BIGINT NOT NULL CHECK (limit_bytes > 0),
    used_bytes     BIGINT NOT NULL DEFAULT 0 CHECK (used_bytes >= 0),
    reserved_bytes BIGINT NOT NULL DEFAULT 0 CHECK (reserved_bytes >= 0),
    CONSTRAINT pools_live_bytes_within_limit
        CHECK (used_bytes + reserved_bytes <= limit_bytes)
);

INSERT INTO pools (pool_id, limit_bytes, used_bytes, reserved_bytes)
VALUES ('shared', 10737418240, 0, 0);

CREATE TABLE mutations (
    user_id              TEXT NOT NULL,
    kind                 TEXT NOT NULL,
    operation_id         TEXT NOT NULL,
    pool_id              TEXT NOT NULL REFERENCES pools (pool_id),
    object_key           TEXT NOT NULL,
    relative_path        TEXT NOT NULL,
    declared_size        BIGINT NOT NULL,
    content_sha256       TEXT,
    prior_size           BIGINT NOT NULL,
    prior_sha256         TEXT,
    growth_bytes         BIGINT NOT NULL,
    state                TEXT NOT NULL,
    external_state       TEXT NOT NULL DEFAULT 'idle',
    dispatch_started_at  TIMESTAMPTZ,
    request_deadline_at  TIMESTAMPTZ,
    worker_lease_until   TIMESTAMPTZ,
    settled_at           TIMESTAMPTZ,
    created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, kind, operation_id),
    CONSTRAINT mutations_kind_check
        CHECK (kind IN ('put', 'delete')),
    CONSTRAINT mutations_declared_size_check
        CHECK (declared_size >= 0),
    CONSTRAINT mutations_prior_size_check
        CHECK (prior_size >= 0),
    CONSTRAINT mutations_growth_bytes_check
        CHECK (growth_bytes >= 0),
    CONSTRAINT mutations_state_check
        CHECK (state IN ('prepared', 'applying', 'succeeded', 'failed', 'rejected')),
    CONSTRAINT mutations_external_state_check
        CHECK (external_state IN ('idle', 'pending', 'settled')),
    CONSTRAINT mutations_dispatch_timing_check CHECK (
        external_state <> 'pending'
        OR (
            dispatch_started_at IS NOT NULL
            AND request_deadline_at IS NOT NULL
            AND worker_lease_until IS NOT NULL
        )
    )
);

CREATE INDEX mutations_active_key_idx
    ON mutations (object_key)
    WHERE state IN ('prepared', 'applying');

CREATE INDEX mutations_active_idx
    ON mutations (state)
    WHERE state IN ('prepared', 'applying');

CREATE INDEX mutations_recovery_idx
    ON mutations (worker_lease_until, request_deadline_at, updated_at)
    WHERE state IN ('prepared', 'applying');

CREATE TABLE key_locks (
    object_key   TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL,
    kind         TEXT NOT NULL,
    operation_id TEXT NOT NULL,
    acquired_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    FOREIGN KEY (user_id, kind, operation_id)
        REFERENCES mutations (user_id, kind, operation_id)
);

CREATE TABLE objects_meta (
    object_key     TEXT PRIMARY KEY,
    pool_id        TEXT NOT NULL REFERENCES pools (pool_id),
    size_bytes     BIGINT NOT NULL,
    content_sha256 TEXT NOT NULL,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT objects_meta_size_bytes_check
        CHECK (size_bytes >= 0)
);