-- $Id: create_tables.pgsql,v 1.27.2.1 2001/06/16 05:47:49 bettina Exp $

    CREATE TABLE phpgw_inv_products (
	con		serial,
	id		varchar(20) NOT NULL,
	serial		varchar(64) NOT NULL,
	name		varchar(255) NOT NULL,
	descr		text,
	category	int,
	status		int,
	weight		int,
	cost		decimal(10,2),
	price		decimal(10,2),
	retail		decimal(10,2),
	stock		int,
	mstock		int,
	url		varchar(255) NOT NULL,
	ftp		varchar(255) NOT NULL,
	dist		int,
	pdate		int,
	sdate		int
    );

    CREATE INDEX phpgw_inv_products_key ON phpgw_inv_products(con,id);

    CREATE table phpgw_inv_statuslist (
	    status_id     serial,
	    status_name   varchar(255) NOT NULL
    );

    CREATE TABLE phpgw_inv_orders (
	    id       	serial,
	    owner    	int,
	    access	char(7),
	    num      	varchar(20) NOT NULL,
	    date     	int,
	    customer 	int,
	    descr    	text,
	    status      text check(status in('open','closed','archive')) DEFAULT 'open' NOT NULL
    );

    CREATE INDEX phpgw_inv_orders_key ON phpgw_inv_orders(id,num);

    CREATE TABLE phpgw_inv_orderpos (
	id           serial,
	order_id     int,
	product_id   int,
	piece        int,
	tax          decimal(6,2),
	dstatus         char(4) DEFAULT 'open' NOT NULL,
	istatus         char(4) DEFAULT 'open' NOT NULL
);

CREATE TABLE phpgw_inv_delivery (
   id       serial,
   num      varchar(20) NOT NULL,
   date     int,
   order_id int
);

CREATE INDEX phpgw_inv_delivery_key ON phpgw_inv_delivery(id,num);

CREATE TABLE phpgw_inv_deliverypos (
   id          serial,
   delivery_id int,
   product_id  int
);

CREATE TABLE phpgw_inv_invoice (
   id              serial,
   num             varchar(20) NOT NULL,
   date            int,
   order_id        int,
   sum             decimal(20,2)
);

CREATE INDEX phpgw_inv_invoice_key ON phpgw_inv_invoice(id,num);

CREATE TABLE phpgw_inv_invoicepos (                                                                                                                                                                        
   id          serial,                                                                                                                                                                                
   invoice_id  int,                                                                                                                                                                  
   product_id  int                                                                                                                                                                            
);

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 ('saled');
insert into phpgw_inv_statuslist (status_name) values ('archive');