connect 'jdbc:derby://localhost:1527/sun-appserv-samples;user=TPCC;password=TPCC';
DROP TABLE TPCC.HISTORY;
DROP TABLE TPCC.NEW_ORDER;
DROP TABLE TPCC.ORDER_LINE;
DROP TABLE TPCC.ORDER_HDR;
DROP TABLE TPCC.STOCK;
DROP TABLE TPCC.ITEM;
DROP TABLE TPCC.CUSTOMER;
DROP TABLE TPCC.DISTRICT;
DROP TABLE TPCC.WAREHOUSE;
DROP TABLE TPCC.IDGENERATOR;
CREATE TABLE TPCC.IDGENERATOR (
	SEQ_NAME VARCHAR(30),
	SEQ_COUNT DECIMAL(15),
	PRIMARY KEY ( SEQ_NAME )
	);
CREATE TABLE TPCC.WAREHOUSE (
	W_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY  (START WITH 1 ,INCREMENT BY 1),
	W_VERSION DECIMAL(15,0) NOT NULL,
	W_NAME VARCHAR(20) NOT NULL,
	W_STREET_1 VARCHAR(20),
	W_STREET_2 VARCHAR(20),
	W_CITY VARCHAR(20) NOT NULL,
	W_STATE CHAR(2),
	W_ZIP CHAR(9),
	W_TAX DECIMAL(4,0),
	W_YTD DECIMAL(12,0),
	PRIMARY KEY(W_ID),
	UNIQUE(W_NAME, W_CITY)
	);
CREATE TABLE TPCC.DISTRICT (
	D_ID INTEGER NOT NULL,
	D_W_ID INTEGER NOT NULL,
	D_VERSION INTEGER NOT NULL,
	D_NAME VARCHAR(10) NOT NULL,
	D_STREET_1 VARCHAR(20),
	D_STREET_2 VARCHAR(20),
	D_CITY VARCHAR(20),
	D_STATE VARCHAR(2),
	D_ZIP VARCHAR(9),
	D_TAX DECIMAL(4,0),
	D_YTD DECIMAL(12,0),
	D_NEXT_O_ID DECIMAL(8, 0),
	PRIMARY KEY(D_W_ID, D_ID),
	FOREIGN KEY(D_W_ID) REFERENCES TPCC.WAREHOUSE ( W_ID )
		ON UPDATE RESTRICT ON DELETE RESTRICT 
	);
INSERT INTO TPCC.IDGENERATOR VALUES('DISTRICT_ID_SEQUENCE', 0);
CREATE TABLE TPCC.CUSTOMER (
	C_ID INTEGER NOT NULL,
	C_D_ID INTEGER NOT NULL,
	C_W_ID INTEGER NOT NULL,
	C_VERSION INTEGER NOT NULL,
	C_FIRST VARCHAR(16),
	C_MIDDLE VARCHAR(2),
	C_LAST VARCHAR(16),
	C_STREET_1 VARCHAR(20),
	C_STREET_2 VARCHAR(20),
	C_CITY VARCHAR(20),
	C_STATE VARCHAR(2),
	C_ZIP VARCHAR(9),
	C_PHONE VARCHAR(16),
	C_SINCE TIMESTAMP,
	C_CREDIT VARCHAR(2),
	C_CREDIT_LIM DECIMAL(12,2),
	C_DISCOUNT DECIMAL(4),
	C_BALANCE DECIMAL(12),
	C_YTD_PAYMENT DECIMAL(12),
	C_PAYMENT_CNT DECIMAL(4),
	C_DELIVERY_CNT DECIMAL(4),
	C_DATA VARCHAR(500),
	PRIMARY KEY(C_W_ID, C_D_ID, C_ID),
	FOREIGN KEY(C_W_ID, C_D_ID) REFERENCES TPCC.DISTRICT ( D_W_ID, D_ID )
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	CHECK (C_CREDIT IN ('GC', 'BC'))
	);
INSERT INTO TPCC.IDGENERATOR VALUES('CUSTOMER_ID_SEQUENCE', 0);
CREATE TABLE TPCC.ITEM (
	I_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 ,INCREMENT BY 1),
	I_VERSION INTEGER NOT NULL,
	I_IM_ID INTEGER,
	I_NAME VARCHAR(24),
	I_PRICE DECIMAL(5,2),
	I_DATA VARCHAR(50),
	PRIMARY KEY (I_ID)
	);
CREATE TABLE TPCC.STOCK (
	S_I_ID INTEGER NOT NULL,
	S_W_ID INTEGER NOT NULL,
	S_VERSION INTEGER NOT NULL,
	S_QUANTITY DECIMAL(4),
	S_DIST_01 VARCHAR(24),
	S_DIST_02 VARCHAR(24),
	S_DIST_03 VARCHAR(24),
	S_DIST_04 VARCHAR(24),
	S_DIST_05 VARCHAR(24),
	S_DIST_06 VARCHAR(24),
	S_DIST_07 VARCHAR(24),
	S_DIST_08 VARCHAR(24),
	S_DIST_09 VARCHAR(24),
	S_DIST_10 VARCHAR(24),
	S_YTD DECIMAL(8,2),
	S_ORDER_CNT DECIMAL(4),
	S_REMOTE_CNT DECIMAL(4),
	S_DATA VARCHAR(50),
	PRIMARY KEY (S_W_ID, S_I_ID),
	FOREIGN KEY (S_W_ID) REFERENCES TPCC.WAREHOUSE (W_ID)
		ON DELETE RESTRICT ON UPDATE RESTRICT,
	FOREIGN KEY (S_I_ID) REFERENCES TPCC.ITEM (I_ID)
		ON DELETE RESTRICT ON UPDATE RESTRICT
	);
CREATE TABLE TPCC.ORDER_HDR (
	O_ID INTEGER NOT NULL,
	O_D_ID INTEGER NOT NULL,
	O_W_ID INTEGER NOT NULL,
	O_C_ID INTEGER NOT NULL,
	O_VERSION INTEGER NOT NULL,
	O_ENTRY_D TIMESTAMP,
	O_CARRIER_ID INTEGER,
	O_OL_CNT INTEGER,
	O_ALL_LOCAL INTEGER,
	PRIMARY KEY(O_W_ID, O_D_ID, O_ID),
	FOREIGN KEY (O_W_ID, O_D_ID, O_C_ID) REFERENCES TPCC.CUSTOMER 
		(C_W_ID, C_D_ID, C_ID) 
		ON UPDATE RESTRICT ON DELETE RESTRICT
	);
INSERT INTO TPCC.IDGENERATOR VALUES('ORDER_ID_SEQUENCE', 0);
CREATE TABLE TPCC.ORDER_LINE (
	OL_O_ID INTEGER NOT NULL,
	OL_D_ID INTEGER NOT NULL,
	OL_W_ID INTEGER NOT NULL,
	OL_NUMBER INTEGER NOT NULL,
	OL_VERSION INTEGER NOT NULL,
	OL_I_ID INTEGER,
	OL_SUPPLY_W_ID INTEGER,
	OL_DELIVERY_D TIMESTAMP,
	OL_QUANTITY DECIMAL(2),
	OL_AMOUNT DECIMAL(2),
	OL_DIST_INFO VARCHAR(24),
	PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER),
	FOREIGN KEY (OL_W_ID, OL_D_ID, OL_O_ID) REFERENCES TPCC.ORDER_HDR
		(O_W_ID, O_D_ID, O_ID)
		ON UPDATE RESTRICT ON DELETE RESTRICT,
	FOREIGN KEY (OL_SUPPLY_W_ID, OL_I_ID) REFERENCES TPCC.STOCK
		(S_W_ID, S_I_ID)
		ON UPDATE RESTRICT ON DELETE RESTRICT
	);
INSERT INTO TPCC.IDGENERATOR VALUES('ORDER_LINE_OLN_SEQUENCE', 0);
CREATE TABLE TPCC.NEW_ORDER (
	NO_O_ID INTEGER NOT NULL,
	NO_D_ID INTEGER NOT NULL,
	NO_W_ID INTEGER NOT NULL,
	PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID),
	FOREIGN KEY (NO_W_ID, NO_D_ID, NO_O_ID) REFERENCES TPCC.ORDER_HDR 
		(O_W_ID, O_D_ID, O_ID)
	);
CREATE TABLE TPCC.HISTORY (
	H_C_ID INTEGER NOT NULL,
	H_C_D_ID INTEGER NOT NULL,
	H_C_W_ID INTEGER NOT NULL,
	H_D_ID INTEGER NOT NULL,
	H_W_ID INTEGER NOT NULL,
	H_DATE TIMESTAMP,
	H_AMOUNT DECIMAL(6,2),
	H_DATA VARCHAR(24),
	FOREIGN KEY (H_C_W_ID, H_C_D_ID, H_C_ID) REFERENCES TPCC.CUSTOMER
		(C_W_ID, C_D_ID, C_ID),
	FOREIGN KEY (H_W_ID, H_D_ID) REFERENCES TPCC.DISTRICT
		(D_W_ID, D_ID)
	);