DROP TABLE IF EXISTS capacity_allocations;
DROP TABLE IF EXISTS capacity_pools;
DROP TABLE IF EXISTS capacity_counters;

CREATE TABLE capacity_pools (
    pool_id TEXT PRIMARY KEY,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    version INTEGER NOT NULL CHECK (version >= 0)
);

CREATE TABLE capacity_allocations (
    pool_id TEXT NOT NULL REFERENCES capacity_pools(pool_id) ON DELETE CASCADE,
    allocation_id TEXT NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (pool_id, allocation_id)
);

CREATE TABLE capacity_counters (
    pool_id TEXT PRIMARY KEY,
    capacity INTEGER NOT NULL CHECK (capacity > 0),
    reserved INTEGER NOT NULL CHECK (reserved >= 0 AND reserved <= capacity)
);

INSERT INTO capacity_pools (pool_id, capacity, version)
VALUES ('pool-1', 10, 0);

INSERT INTO capacity_counters (pool_id, capacity, reserved)
VALUES ('counter-1', 10, 0);
