rFwUSeGk

1drop database if exists store;
2create database store;
3\c store
4begin;
5create table products(
6  id serial primary key,
7  name varchar(200) not null,
8  created_at timestamp default current_timestamp,
9  updated_at timestamp default current_timestamp
10);
11create table skus(
12  sku varchar(20) primary key,
13  option varchar(20),
14  created_at timestamp default current_timestamp,
15  updated_at timestamp default current_timestamp,
16  product_id integer references products (id)
17);
18create table prices(
19  id serial primary key,
20  cents integer NOT NULL,
21  created_at timestamp default current_timestamp,
22  updated_at timestamp default current_timestamp,
23  sku varchar(20) references skus (sku)
24);
25create table purchases(
26  id serial primary key,
27  purchase_date timestamp
28);
29create table order_items(
30  id serial primary key,
31  quantity integer NOT NULL,
32  sku varchar(20) references skus (sku),
33  purchase_id integer references purchases (id)
34);
35do $$
36begin
37for i in 1..10 loop
38  insert into products (name) values ('Example Product ' || i);
39  for b in 1..5 loop
40    insert into skus (sku, product_id) values ('SKU_' || i || '_' || b, i);
41    for c in 1..3 loop
42      insert into prices (cents, sku) values (floor(random() * 10000), 'SKU_' || i || '_' || b);
43    end loop;
44  end loop;
45end loop;
46for i in 1..20 loop
47  insert into purchases default values;
48  for b in 1..(floor(random() * 5) + 1) loop
49    insert into order_items (quantity, sku, purchase_id) values (floor(random() * 3) + 1, 'SKU_' || floor(random() * 10) + 1 || '_' || floor(random() * 5) + 1, i);
50  end loop;
51end loop;
52end;
53$$;
54commit;