Make picture list nicer

[app-web-oof.git] / db.sql

CREATE TABLE IF NOT EXISTS products (
	product  serial PRIMARY KEY,
	title    TEXT   NOT NULL,
	subtitle TEXT   NOT NULL,
	summary  TEXT   NOT NULL,
	price    INT    NOT NULL,
	stock    INT    NOT NULL,
	CONSTRAINT positive_stock CHECK (stock >= 0)
);

CREATE TABLE IF NOT EXISTS discounts (
	discount VARCHAR(20) PRIMARY KEY,
	fraction DECIMAL,
	flat     INT,
	CONSTRAINT fraction_xor_flat CHECK ((fraction IS NULL AND flat IS NOT NULL) OR (fraction IS NOT NULL AND flat IS NULL))
);

CREATE TABLE IF NOT EXISTS orders (
	id       TEXT PRIMARY KEY,
	products JSON NOT NULL,
	total    INT NOT NULL,
	discount VARCHAR(20) REFERENCES discounts UNIQUE,

	stripe_token TEXT,

	-- DELIVERY
	first_name   VARCHAR(20) NOT NULL,
	last_name    VARCHAR(20) NOT NULL,
	email        VARCHAR(80) NOT NULL,
	phone        VARCHAR(20),
	postcode     VARCHAR(10) NOT NULL,
	address1     VARCHAR(32) NOT NULL,
	address2     VARCHAR(32),
	address3     VARCHAR(32),
	address4     VARCHAR(32),
	safe_place   VARCHAR(32),
	instructions VARCHAR(32)
);