DROP TABLE C_customerinventory;
CREATE TABLE C_customerinventory
  (ci_id 		integer not null,
   ci_itemid 	char(20),
   ci_quantity	integer,             
   ci_customerid	integer,
   ci_value 		numeric(12,2));

CREATE UNIQUE INDEX C_ci_idx ON C_customerinventory(ci_id);

DROP TABLE C_customer;
CREATE TABLE C_customer
(
	c_id		integer not null,
	c_first		char(16),
	c_last		char(16),
	c_street1	char(20),	
	c_street2	char(20),	
	c_city		char(20),	
	c_state		char(2),	
	c_country	char(10),	
	c_zip		char(9),	
	c_phone		char(16),
	c_contact	char(25),	
	c_since		date,
	c_balance	numeric(12,2),
	c_credit	char(2),
	c_credit_limit	numeric(12,2),
	c_ytd_payment	numeric(12,2)
);

CREATE UNIQUE INDEX C_c_idx ON C_customer (c_id);

DROP TABLE C_supplier;
CREATE TABLE C_supplier
(
	supp_id			integer not null,
	supp_name		char(16),
	supp_street1	char(20),	
	supp_street2	char(20),	
	supp_city		char(20),	
	supp_state		char(2),	
	supp_country	char(10),	
	supp_zip		char(9),	
	supp_phone		char(16),
	supp_contact	char(25)
);

CREATE UNIQUE INDEX C_supp_idx ON C_supplier (supp_id);

DROP TABLE C_site;
CREATE TABLE C_site
(
	site_id			integer not null,
	site_name		char(16),
	site_street1	char(20),	
	site_street2	char(20),	
	site_city		char(20),	
	site_state		char(2),	
	site_country	char(10),	
	site_zip		char(9)
);

CREATE UNIQUE INDEX C_site_idx ON C_site (site_id);

DROP TABLE C_parts;
CREATE TABLE C_parts
(
	p_id			char(20) not null,
	p_name			char(35),
	p_desc			varchar(100),
	p_rev			char(6),
	p_unit			char(10),
	p_cost			numeric(12,2),
	p_price			numeric(12,2),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
        p_lomark                integer,
        p_himark                integer
);

CREATE UNIQUE INDEX C_p_idx ON C_parts (p_id);
DROP TABLE M_parts;
CREATE TABLE M_parts
(
	p_id			char(20) not null,
	p_name			char(35),
	p_desc			varchar(100),
	p_rev			char(6),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
	p_lomark		integer,
	p_himark		integer
);

CREATE UNIQUE INDEX M_parts_idx ON M_parts (p_id);

DROP TABLE M_bom;
CREATE TABLE M_bom
(
	b_comp_id		char(20) not null,
	b_assembly_id		char(20) not null,
	b_line_no		integer,
	b_qty			integer,
	b_ops			integer,	
	b_eng_change		char(10),	
	b_ops_desc		varchar(100)
);

CREATE UNIQUE INDEX M_bom_idx ON M_bom (b_assembly_id, b_comp_id, b_line_no);

DROP TABLE M_workorder;
CREATE TABLE M_workorder
(
	wo_number		integer not null,
	wo_o_id			integer,
	wo_ol_id		integer,
	wo_status		integer,
	wo_assembly_id	char(20),
	wo_orig_qty		integer,
	wo_comp_qty		integer,
	wo_due_date		date,
	wo_start_date		timestamp
);

CREATE UNIQUE INDEX M_wo_idx ON M_workorder (wo_number);

DROP TABLE M_largeorder;
CREATE TABLE M_largeorder
(
	lo_id			integer not null,
	lo_o_id			integer,
	lo_ol_id		integer,
	lo_assembly_id	char(20),
	lo_qty			integer,
	lo_due_date		date,
        lo_category             integer
);

DROP TABLE M_inventory;
CREATE TABLE M_inventory
(
	in_p_id			char(20) not null,
	in_qty			integer,
	in_ordered		integer,
	in_location		char(20),	
	in_acc_code		integer,
	in_act_date		date
);

CREATE UNIQUE INDEX M_inv_idx ON M_inventory (in_p_id);
DROP TABLE O_orders;
CREATE TABLE O_orders
(
	o_id		integer not null,        
	o_c_id		integer,
	o_ol_cnt	integer,
	o_discount	numeric(4,2),
	o_total		numeric(12,2),
	o_status	integer,
	o_entry_date	timestamp,
	o_ship_date	date
);

CREATE UNIQUE INDEX O_ords_idx ON O_orders (o_id);

CREATE INDEX O_oc_idx ON O_orders (o_c_id);

DROP TABLE O_orderline;
CREATE TABLE O_orderline
(
	ol_id		integer not null,
	ol_o_id		integer not null,
	ol_i_id		char(20),
	ol_qty		integer,
      ol_total_value    numeric(12,2),
   ol_msrp         numeric(12,2),
	ol_status	integer,
	ol_ship_date	date
);

CREATE UNIQUE INDEX O_ordl_idx ON O_orderline (ol_o_id, ol_id);

DROP TABLE O_item;
CREATE TABLE O_item
(
	i_id			char(20) not null,
	i_name			char(35),
	i_desc			varchar(100),
	i_price			numeric(12,2),
	i_discount		numeric(6,4),
        i_category		integer not null
);

CREATE UNIQUE INDEX O_i_idx ON O_item (i_id);
CREATE INDEX O_icat_idx ON O_item (i_category);

DROP TABLE S_component;
CREATE TABLE S_component      
(
	comp_id			char(20) not null,
	comp_name		char(10),
	comp_desc		varchar(100),
	comp_unit		char(10),
	comp_cost		numeric(12,2),
	qty_on_order		integer,
	qty_demanded		integer,
	lead_time		integer,
	container_size		integer
);

CREATE UNIQUE INDEX S_comp_idx ON S_component (comp_id);

DROP TABLE S_supp_component;
CREATE TABLE S_supp_component
(
	sc_p_id			char(20) not null,
	sc_supp_id		integer not null,
	sc_price		numeric(12,2),
	sc_qty			integer,
	sc_discount		numeric(6,4),
	sc_del_date		integer
);

CREATE UNIQUE INDEX S_sc_idx ON S_supp_component (sc_p_id, sc_supp_id);

DROP TABLE S_supplier;
CREATE TABLE S_supplier
(
	supp_id			integer not null,
	supp_name		char(16),
	supp_street1	char(20),	
	supp_street2	char(20),	
	supp_city		char(20),	
	supp_state		char(2),	
	supp_country	char(10),	
	supp_zip		char(9),	
	supp_phone		char(16),
	supp_contact	char(25)
);

CREATE UNIQUE INDEX S_supp_idx ON S_supplier (supp_id);

DROP TABLE S_site;
CREATE TABLE S_site
(
	site_id			integer not null,
	site_name		char(16),
	site_street1	char(20),	
	site_street2	char(20),	
	site_city		char(20),	
	site_state		char(2),	
	site_country	char(10),	
	site_zip		char(9)
);

CREATE UNIQUE INDEX S_site_idx ON S_site (site_id);

DROP TABLE S_purchase_order;
CREATE TABLE S_purchase_order
(
	po_number		integer not null,
	po_supp_id		integer,
	po_site_id		integer
);

CREATE UNIQUE INDEX S_po_idx ON S_purchase_order (po_number);

DROP TABLE S_purchase_orderline;
CREATE TABLE S_purchase_orderline
(
	pol_number		integer not null,
	pol_po_id		integer not null,
	pol_p_id		char(20),
	pol_qty			integer,
	pol_balance		numeric(12,2),
	pol_deldate		date,
	pol_message		varchar(100)
);

CREATE UNIQUE INDEX S_pol_idx ON S_purchase_orderline (pol_po_id, pol_number);

DROP TABLE U_sequences;
CREATE TABLE U_sequences
(
	s_id		varchar(50) not null,
	s_nextnum	integer,
	s_blocksize	integer
);

CREATE UNIQUE INDEX U_s_idx ON U_sequences (s_id);
