OPT_HOST="-h localhost"
USER=postgres

for x in $(psql $OPT_HOST -c "SELECT datname FROM pg_database WHERE datname ~ 'shard.*' " postgres -A -t)

do
	echo dropping $x
	dropdb $OPT_HOST $x
done;

createdb $OPT_HOST shard

psql $OPT_HOST -U $USER shard -c "
	CREATE EXTENSION postgres_fdw;
	CREATE TABLE orders(customer_id int, product_id int)
	       PARTITION BY LIST (customer_id);"
createdb $OPT_HOST shard_0
psql $OPT_HOST -U $USER shard_0 -c "
		CREATE TABLE orders_0 AS
		SELECT trunc(2 * random())::int AS customer_id,
		       trunc(100 * random())::int AS product_id
		FROM generate_series(1, 5000);
		ANALYZE;"
psql $OPT_HOST -U $USER shard -c "
		CREATE SERVER server_0 FOREIGN DATA WRAPPER postgres_fdw
			OPTIONS (host 'localhost', dbname 'shard_0', fetch_size '1000',
			use_remote_estimate 'true', fdw_tuple_cost '10');
		CREATE USER MAPPING FOR CURRENT_USER SERVER server_0 OPTIONS (user '$USER');
		IMPORT FOREIGN SCHEMA public FROM SERVER server_0 INTO public;
		ALTER TABLE orders ATTACH PARTITION orders_0 FOR VALUES IN (0, 1);"

createdb $OPT_HOST shard_1
psql $OPT_HOST -U $USER shard_1 -c "
		CREATE TABLE orders_1 AS
		SELECT trunc(2 * random())::int + 2 AS customer_id,
		       trunc(100 * random())::int + 2 AS product_id
		FROM generate_series(1, 5000);
		ANALYZE;"
psql $OPT_HOST -U $USER shard -c "
		CREATE SERVER server_1 FOREIGN DATA WRAPPER postgres_fdw
			OPTIONS (host 'localhost', dbname 'shard_1', fetch_size '1000',
			use_remote_estimate 'true', fdw_tuple_cost '10');
		CREATE USER MAPPING FOR CURRENT_USER SERVER server_1 OPTIONS (user '$USER');
		IMPORT FOREIGN SCHEMA public FROM SERVER server_1 INTO public;
		ALTER TABLE orders ATTACH PARTITION orders_1 FOR VALUES IN (2, 3);"


psql $OPT_HOST -U $USER shard -c "SELECT * FROM orders"