# $Id: create_tables.mysql,v 1.29 2001/06/10 03:19:15 bettina Exp $ 

	CREATE TABLE phpgw_inv_products (
		con				int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		id				varchar(20) NOT NULL,
		serial			varchar(64) NOT NULL,
		name			varchar(255) NOT NULL,
		descr			text,
		category		int(9) DEFAULT '0' NOT NULL,
		status			int(11) DEFAULT '0' NOT NULL,
		weight			int(11) DEFAULT '0' NOT NULL,
		cost			decimal(10,2) DEFAULT '0' NOT NULL,
		price			decimal(10,2) DEFAULT '0' NOT NULL,
		retail			decimal(10,2) DEFAULT '0' NOT NULL,
		stock			int(11) DEFAULT '0' NOT NULL,
		mstock			int(11) DEFAULT '0' NOT NULL,
		url				varchar(255) NOT NULL,
		ftp				varchar(255) NOT NULL,
		dist			int(11) DEFAULT '0' NOT NULL,
		pdate			int(11) DEFAULT '0' NOT NULL,
		sdate			int(11) DEFAULT '0' NOT NULL,
		bin				int(11) DEFAULT '0' NOT NULL,
		product_note	text
	);

	CREATE INDEX phpgw_inv_products_key ON phpgw_inv_products(con,id);

	create table phpgw_inv_statuslist (
		status_id	int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		status_name	varchar(255) NOT NULL
	);

	CREATE TABLE phpgw_inv_orders (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		owner		int(11) DEFAULT '0' NOT NULL,
		access		varchar(7),
		num			varchar(20) NOT NULL,
		date		int(11) DEFAULT '0' NOT NULL,
		customer	int(11) DEFAULT '0' NOT NULL,
		descr		text,
		status		enum('open','closed','archive') NOT NULL
	);

	CREATE INDEX phpgw_inv_orders_key ON phpgw_inv_orders(id,num);
	CREATE UNIQUE INDEX order_num ON phpgw_inv_orders(num);

	CREATE TABLE phpgw_inv_orderpos (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		order_id	int(11) DEFAULT '0' NOT NULL,
		product_id	int(11) DEFAULT '0' NOT NULL,
		piece		int(11) DEFAULT '0' NOT NULL,
		tax			decimal(6,2) DEFAULT '0' NOT NULL
	);

	CREATE TABLE phpgw_inv_delivery (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		num			varchar(20) NOT NULL,
		date		int(11) DEFAULT '0' NOT NULL,
		order_id	int(11) DEFAULT '0' NOT NULL
	);

	CREATE INDEX phpgw_inv_delivery_key ON phpgw_inv_delivery(id,num);
	CREATE UNIQUE INDEX delivery_num ON phpgw_inv_delivery(num);

	CREATE TABLE phpgw_inv_deliverypos (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		delivery_id	int(11) DEFAULT '0' NOT NULL,
		product_id	int(11) DEFAULT '0' NOT NULL
	);

	CREATE TABLE phpgw_inv_invoice (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		num			varchar(20) NOT NULL,
		date		int(11) DEFAULT '0' NOT NULL,
		order_id	int(11) DEFAULT '0' NOT NULL,
		sum			decimal(20,2) DEFAULT '0' NOT NULL
	);

	CREATE INDEX phpgw_inv_invoice_key ON phpgw_inv_invoice(id,num);
	CREATE UNIQUE INDEX invoice_num ON phpgw_inv_invoice(num);

	CREATE TABLE phpgw_inv_invoicepos (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		invoice_id	int(11) DEFAULT '0' NOT NULL,
		product_id	int(11) DEFAULT '0' NOT NULL
	);

	CREATE TABLE phpgw_inv_stockrooms (
		id			int(11) unsigned PRIMARY KEY NOT NULL auto_increment,
		room_owner	int(11) DEFAULT '0' NOT NULL,
		room_access	varchar(7),
		room_name	varchar(255) NOT NULL,
		room_note	text
	);

	insert into phpgw_inv_statuslist (status_name) values ('available');
	insert into phpgw_inv_statuslist (status_name) values ('no longer available');
	insert into phpgw_inv_statuslist (status_name) values ('back order');
	insert into phpgw_inv_statuslist (status_name) values ('unknown');
	insert into phpgw_inv_statuslist (status_name) values ('other');
	insert into phpgw_inv_statuslist (status_name) values ('sold');
	insert into phpgw_inv_statuslist (status_name) values ('archive');
