# $Id: create_tables.mysql,v 1.25.2.1 2001/06/16 05:47:49 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
);

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		char(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 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,
		dstatus			char(4) DEFAULT 'open' NOT NULL,
		istatus			char(4) DEFAULT 'open' 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 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 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
);

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');
