CREATE TABLE products (
	shortdsc	char(11) NOT NULL,
	longdsc		text NOT NULL,
	sellprice	numeric(11,2),
	stock		rational NOT NULL,
	unit		char(20) NOT NULL,
	active		bool NOT NULL DEFAULT 'true',
	prodid		serial
);
CREATE UNIQUE INDEX products_shortdsc_index ON products (shortdsc);

CREATE TABLE measurementunits (
	unit		char(20) UNIQUE NOT NULL
);
CREATE TABLE transform (
	prodid_out	int NOT NULL,
	notes		text,
	inactivation	timestamp DEFAULT NULL,
	transformid	serial
);
CREATE INDEX transform_prodid_out_index ON transform (prodid_out);

CREATE TABLE transform_ingredient (
	transformid	int NOT NULL,
	prodid_in	int NOT NULL,
	amount		rational
);
CREATE INDEX transform_ing_transformid_index ON transform_ingredient (transformid);

CREATE TABLE howtomake_ingredient (
	prodid_out	int NOT NULL,
	prodid_in	int NOT NULL,
	amount		rational
);
CREATE INDEX howtomake_ing_prodid_out_index ON howtomake_ingredient (prodid_out);

CREATE TABLE deviationtype (
	shortdsc	char(16) NOT NULL,
	longdsc		text NOT NULL,
	devid		serial
);

CREATE TABLE operations (
	t		timestamp NOT NULL,
	notes		text NOT NULL,
	userid		char(20) NOT NULL,
	opid		serial
);

CREATE TABLE tradehistory (
	opid		int NOT NULL,
	prodid		int NOT NULL,
	amount		rational NOT NULL,
	price		numeric(11,2)
);

CREATE TABLE inventorydeviation (
	opid		int NOT NULL,
	devid		int NOT NULL,
	prodid		int NOT NULL,
	diff		rational
);

CREATE VIEW ProdsToSell
AS SELECT
	shortdsc,
	longdsc,
	sellprice,
	unit,
	prodid
FROM products
WHERE active AND sellprice IS NOT NULL;

CREATE VIEW ProdsToBuy
AS SELECT
	shortdsc,
	longdsc,
	stock,
	unit,
	prodid
FROM products
WHERE active AND prodid NOT IN (SELECT prodid_out FROM transform WHERE active);