BEGIN WORK;

CREATE TABLE products (
  productcode VARCHAR(32) NOT NULL,
  description VARCHAR(64) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY(productcode),
  CONSTRAINT code_not_empty CHECK(productcode <> ''),
  CONSTRAINT product_not_free CHECK(price > 0)
);


CREATE SEQUENCE customers_customerid_seq;

CREATE TABLE customers (
  customerid INT NOT NULL DEFAULT nextval('customers_customerid_seq'),
  customer_name VARCHAR(64) NOT NULL,
  PRIMARY KEY(customerid)
);

ALTER SEQUENCE customers_customerid_seq OWNED BY customers.customerid;


-- Create few customers
INSERT INTO customers (customer_name)
SELECT 'Some Customer'
FROM generate_series(1,10) AS a(a);


CREATE SEQUENCE sales_orders_saleid_seq;

CREATE TABLE sales_orders (
  saleid INT NOT NULL DEFAULT nextval('sales_orders_saleid_seq'),
  customerid INT NOT NULL, -- REFERENCES customers (customerid) ON UPDATE CASCADE ON DELETE RESTRICT,
  productcode VARCHAR(32) NOT NULL, -- REFERENCES products (productcode) ON UPDATE CASCADE ON DELETE RESTRICT,
  quantity FLOAT NOT NULL,
  PRIMARY KEY(saleid)
--  CONSTRAINT quantity_above_zero CHECK(quantity > 0)
);

ALTER SEQUENCE sales_orders_saleid_seq OWNED BY sales_orders.saleid;

CREATE INDEX sales_orders_customerid_idx ON sales_orders (customerid);

ALTER TABLE sales_orders CLUSTER ON sales_orders_customerid_idx;

-- Create 10,000 products.
INSERT INTO products (productcode,description,price)
SELECT LPAD(a.a::VARCHAR,5,'0'),'Some Product',RANDOM() * 100 + 1
FROM generate_series(0,9999) AS a(a);


-- Start to populate the sales_orders table. The
-- first time around we don't have any skew at all.
-- Each customer has ordered the same products
-- the same amount of times.

INSERT INTO sales_orders (customerid,productcode,quantity)
SELECT cust.customerid,prod.productcode,RANDOM() * 10
FROM customers AS cust
CROSS JOIN products AS prod
CROSS JOIN generate_series(1,100);


-- Now we introduce skew to the table.
-- Since our product codes are numerical, we use the
-- customerid number to divide the product code then
-- we mutiply it again by the customer code. This means
-- that customer with the id 5 will only have product
-- codes that are evenly divisble by 5 when cast as int.
-- Basically the idea here is to have customer with the id
-- 1 a non skew set then slowly introduce skew the higher
-- the customerid gets... Here goes.

INSERT INTO sales_orders (customerid,productcode,quantity)
SELECT cust.customerid,
       LPAD((prod.productcode::INT / cust.customerid * cust.customerid)::VARCHAR,5,'0') AS trunc_code,
       RANDOM() * 10
FROM customers AS cust
CROSS JOIN products AS prod
CROSS JOIN generate_series(1,100);


-- Now each customer should have equal number of rows
-- in sales_orders, however the mix of products ordered
-- becomes less even the higher the customerid.


-- Now to get the tables in the best possible shape.
-- We cluster by customerid in sales_orders to cut
-- down seak overhead around the heap from index
-- scanning. This keeps it consistent.

COMMIT;

VACUUM ANALYZE;
CLUSTER;
REINDEX TABLE sales_orders;


-- Now the query...

SELECT SUM(sales.quantity * prod.price) AS total_cost
FROM sales_orders AS sales
INNER JOIN products AS prod ON sales.productcode = prod.productcode
WHERE sales.customerid = 10;
