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

CREATE TABLE measurementunits (
	unit		char(20)
);
CREATE TABLE transform (
	prodid_out	int NOT NULL,
	notes		text,
	active		bool,
	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_ingredient_transformid_index ON transform_ingredient (transformid);

CREATE TABLE transform_history (
	transformid	int,
	inactivation	timestamp
);
CREATE INDEX transform_history_transformid_index ON transform_history (transformid);

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

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

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

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

CREATE TABLE inventorydeviation (
	opid		int,
	devid		int,
	prodid		int,
	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);