CREATE TABLE IF NOT EXISTS allocations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pool_id INTEGER NOT NULL REFERENCES pools(id),
    cidr TEXT NOT NULL UNIQUE,
    prefix_length INTEGER NOT NULL,
    address_count INTEGER NOT NULL,
    usable_host_count INTEGER NOT NULL,
    note TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('active', 'reserved', 'released')),
    created_at TEXT NOT NULL,
    created_by TEXT NOT NULL DEFAULT 'local',
    released_at TEXT NULL,
    released_by TEXT NULL,
    release_note TEXT NULL
);

CREATE INDEX IF NOT EXISTS idx_allocations_pool_id ON allocations(pool_id);
CREATE INDEX IF NOT EXISTS idx_allocations_status ON allocations(status);
CREATE INDEX IF NOT EXISTS idx_allocations_pool_status ON allocations(pool_id, status);
CREATE INDEX IF NOT EXISTS idx_allocations_created_at ON allocations(created_at DESC);