// Copyright 2000-2004 Apache Software Foundation
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//    http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
//  $Id$
//
CREATE TABLE ACCOUNT(USERID VARCHAR(80) NOT NULL PRIMARY KEY,EMAIL VARCHAR(80) NOT NULL,FIRSTNAME VARCHAR(80) NOT NULL,LASTNAME VARCHAR(80) NOT NULL,STATUS VARCHAR(2),ADDR1 VARCHAR(80) NOT NULL,ADDR2 VARCHAR(40),CITY VARCHAR(80) NOT NULL,STATE VARCHAR(80) NOT NULL,ZIP VARCHAR(20) NOT NULL,COUNTRY VARCHAR(20) NOT NULL,PHONE VARCHAR(80) NOT NULL)
CREATE TABLE SUPPLIER(SUPPID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(80),STATUS VARCHAR(2) NOT NULL,ADDR1 VARCHAR(80),ADDR2 VARCHAR(80),CITY VARCHAR(80),STATE VARCHAR(80),ZIP VARCHAR(5),PHONE VARCHAR(80))
CREATE TABLE SIGNON(USERNAME VARCHAR(25) NOT NULL PRIMARY KEY,PASSWORD VARCHAR(25) NOT NULL)
CREATE TABLE PROFILE(USERID VARCHAR(80) NOT NULL PRIMARY KEY,LANGPREF VARCHAR(80) NOT NULL,FAVCATEGORY VARCHAR(30),MYLISTOPT BIT,BANNEROPT BIT)
CREATE TABLE BANNERDATA(FAVCATEGORY VARCHAR(80) NOT NULL PRIMARY KEY,IMAGEPATH VARCHAR(80))
CREATE TABLE ORDERS(ORDERID INTEGER NOT NULL PRIMARY KEY,USERID VARCHAR(80) NOT NULL,ORDERDATE DATE NOT NULL,SHIPADDR1 VARCHAR(80) NOT NULL,SHIPADDR2 VARCHAR(80),SHIPCITY VARCHAR(80) NOT NULL,SHIPSTATE VARCHAR(80) NOT NULL,SHIPZIP VARCHAR(20) NOT NULL,SHIPCOUNTRY VARCHAR(20) NOT NULL,BILLADDR1 VARCHAR(80) NOT NULL,BILLADDR2 VARCHAR(80),BILLCITY VARCHAR(80) NOT NULL,BILLSTATE VARCHAR(80) NOT NULL,BILLZIP VARCHAR(20) NOT NULL,BILLCOUNTRY VARCHAR(20) NOT NULL,COURIER VARCHAR(80) NOT NULL,TOTALPRICE DECIMAL(10,2) NOT NULL,BILLTOFIRSTNAME VARCHAR(80) NOT NULL,BILLTOLASTNAME VARCHAR(80) NOT NULL,SHIPTOFIRSTNAME VARCHAR(80) NOT NULL,SHIPTOLASTNAME VARCHAR(80) NOT NULL,CREDITCARD VARCHAR(80) NOT NULL,EXPRDATE VARCHAR(7) NOT NULL,CARDTYPE VARCHAR(80) NOT NULL,LOCALE VARCHAR(80) NOT NULL)
CREATE TABLE ORDERSTATUS(ORDERID INTEGER NOT NULL,LINENUM INTEGER NOT NULL,TIMESTAMP DATE NOT NULL,STATUS VARCHAR(2) NOT NULL,CONSTRAINT PK_ORDERSTATUS PRIMARY KEY(ORDERID,LINENUM))
CREATE TABLE LINEITEM(ORDERID INTEGER NOT NULL,LINENUM INTEGER NOT NULL,ITEMID VARCHAR(10) NOT NULL,QUANTITY INTEGER NOT NULL,UNITPRICE DECIMAL(10,2) NOT NULL,CONSTRAINT PK_LINEITEM PRIMARY KEY(ORDERID,LINENUM))
CREATE TABLE CATEGORY(CATID VARCHAR(10) NOT NULL PRIMARY KEY,NAME VARCHAR(80),IMAGEPATH VARCHAR(80))
CREATE TABLE PRODUCT(PRODUCTID VARCHAR(10) NOT NULL PRIMARY KEY,CATEGORY VARCHAR(10) NOT NULL,NAME VARCHAR(80),IMAGEPATH VARCHAR(80),DESCN VARCHAR(255),CONSTRAINT FK_PRODUCT_1 FOREIGN KEY(CATEGORY) REFERENCES CATEGORY(CATID))
CREATE INDEX PRODUCTCAT ON PRODUCT(CATEGORY)
CREATE INDEX PRODUCTNAME ON PRODUCT(NAME)
CREATE TABLE ITEM(ITEMID VARCHAR(10) NOT NULL PRIMARY KEY,PRODUCTID VARCHAR(10) NOT NULL,LISTPRICE DECIMAL(10,2),UNITCOST DECIMAL(10,2),SUPPLIER INTEGER,STATUS VARCHAR(2),ATTR1 VARCHAR(80),ATTR2 VARCHAR(80),ATTR3 VARCHAR(80),ATTR4 VARCHAR(80),ATTR5 VARCHAR(80),CONSTRAINT FK_ITEM_1 FOREIGN KEY(PRODUCTID) REFERENCES PRODUCT(PRODUCTID),CONSTRAINT FK_ITEM_2 FOREIGN KEY(SUPPLIER) REFERENCES SUPPLIER(SUPPID))
CREATE INDEX ITEMPROD ON ITEM(PRODUCTID)
CREATE TABLE INVENTORY(ITEMID VARCHAR(10) NOT NULL PRIMARY KEY,QTY INTEGER NOT NULL)
CREATE TABLE SEQUENCE(NAME VARCHAR(30) NOT NULL PRIMARY KEY,NEXTID INTEGER NOT NULL)
CREATE USER SA PASSWORD "" ADMIN
INSERT INTO ACCOUNT VALUES('ACID','acid@yourdomain.com','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO ACCOUNT VALUES('j2ee','yourname@yourdomain.com','ABC','XYX','OK','901 San Antonio Road','MS UCUP02-206','Palo Alto','CA','94303','USA','555-555-5555')
INSERT INTO SUPPLIER VALUES(1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797')
INSERT INTO SUPPLIER VALUES(2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797')
INSERT INTO SIGNON VALUES('ACID','ACID')
INSERT INTO SIGNON VALUES('j2ee','j2ee')
INSERT INTO PROFILE VALUES('ACID','english','CATS',1,1)
INSERT INTO PROFILE VALUES('j2ee','english','DOGS',1,1)
INSERT INTO BANNERDATA VALUES('BIRDS','../images/banner_birds.gif')
INSERT INTO BANNERDATA VALUES('CATS','../images/banner_cats.gif')
INSERT INTO BANNERDATA VALUES('DOGS','../images/banner_dogs.gif')
INSERT INTO BANNERDATA VALUES('FISH','../images/banner_fish.gif')
INSERT INTO BANNERDATA VALUES('REPTILES','../images/banner_reptiles.gif')
INSERT INTO CATEGORY VALUES('BIRDS','Birds','../images/birds_icon.gif')
INSERT INTO CATEGORY VALUES('CATS','Cats','../images/cats_icon.gif')
INSERT INTO CATEGORY VALUES('DOGS','Dogs','../images/dogs_icon.gif')
INSERT INTO CATEGORY VALUES('FISH','Fish','../images/fish_icon.gif')
INSERT INTO CATEGORY VALUES('REPTILES','Reptiles','../images/reptiles_icon.gif')
INSERT INTO PRODUCT VALUES('AV-CB-01','BIRDS','Amazon Parrot','../images/bird4.gif','Great companion for up to 75 years')
INSERT INTO PRODUCT VALUES('AV-SB-02','BIRDS','Finch','../images/bird1.gif','Great stress reliever')
INSERT INTO PRODUCT VALUES('FI-FW-01','FISH','Koi','../images/fish3.gif','Fresh Water fish from Japan')
INSERT INTO PRODUCT VALUES('FI-FW-02','FISH','Goldfish','../images/fish2.gif','Fresh Water fish from China')
INSERT INTO PRODUCT VALUES('FI-SW-01','FISH','Angelfish','../images/fish1.jpg','Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FI-SW-02','FISH','Tiger Shark','../images/fish4.gif','Salt Water fish from Australia')
INSERT INTO PRODUCT VALUES('FL-DLH-02','CATS','Persian','../images/cat1.gif','Friendly house cat, doubles as a princess')
INSERT INTO PRODUCT VALUES('FL-DSH-01','CATS','Manx','../images/cat3.gif','Great for reducing mouse populations')
INSERT INTO PRODUCT VALUES('K9-BD-01','DOGS','Bulldog','../images/dog2.gif','Friendly dog from England')
INSERT INTO PRODUCT VALUES('K9-CW-01','DOGS','Chihuahua','../images/dog4.gif','Great companion dog')
INSERT INTO PRODUCT VALUES('K9-DL-01','DOGS','Dalmation','../images/dog5.gif','Great dog for a Fire Station')
INSERT INTO PRODUCT VALUES('K9-PO-02','DOGS','Poodle','../images/dog6.gif','Cute dog from France')
INSERT INTO PRODUCT VALUES('K9-RT-01','DOGS','Golden Retriever','../images/dog1.gif','Great family dog')
INSERT INTO PRODUCT VALUES('K9-RT-02','DOGS','Labrador Retriever','../images/dog5.gif','Great hunting dog')
INSERT INTO PRODUCT VALUES('RP-LI-02','REPTILES','Iguana','../images/lizard2.gif','Friendly green friend')
INSERT INTO PRODUCT VALUES('RP-SN-01','REPTILES','Rattlesnake','../images/lizard3.gif','Doubles as a watch dog')
INSERT INTO ITEM VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-19','AV-SB-02',15.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-20','FI-FW-02',5.50,2.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-21','FI-FW-02',5.29,1.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-22','K9-RT-02',135.50,100.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-23','K9-RT-02',145.49,100.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-24','K9-RT-02',255.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-25','K9-RT-02',325.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-26','K9-CW-01',125.50,92.00,1,'P','Adult Male',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-27','K9-CW-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-28','K9-RT-01',155.29,90.00,1,'P','Adult Female',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO ITEM VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy',NULL,NULL,NULL,NULL)
INSERT INTO INVENTORY VALUES('EST-1',10000)
INSERT INTO INVENTORY VALUES('EST-10',10000)
INSERT INTO INVENTORY VALUES('EST-11',10000)
INSERT INTO INVENTORY VALUES('EST-12',10000)
INSERT INTO INVENTORY VALUES('EST-13',10000)
INSERT INTO INVENTORY VALUES('EST-14',10000)
INSERT INTO INVENTORY VALUES('EST-15',10000)
INSERT INTO INVENTORY VALUES('EST-16',10000)
INSERT INTO INVENTORY VALUES('EST-17',10000)
INSERT INTO INVENTORY VALUES('EST-18',10000)
INSERT INTO INVENTORY VALUES('EST-19',10000)
INSERT INTO INVENTORY VALUES('EST-2',10000)
INSERT INTO INVENTORY VALUES('EST-20',10000)
INSERT INTO INVENTORY VALUES('EST-21',10000)
INSERT INTO INVENTORY VALUES('EST-22',10000)
INSERT INTO INVENTORY VALUES('EST-23',10000)
INSERT INTO INVENTORY VALUES('EST-24',10000)
INSERT INTO INVENTORY VALUES('EST-25',10000)
INSERT INTO INVENTORY VALUES('EST-26',10000)
INSERT INTO INVENTORY VALUES('EST-27',10000)
INSERT INTO INVENTORY VALUES('EST-28',10000)
INSERT INTO INVENTORY VALUES('EST-3',10000)
INSERT INTO INVENTORY VALUES('EST-4',10000)
INSERT INTO INVENTORY VALUES('EST-5',10000)
INSERT INTO INVENTORY VALUES('EST-6',10000)
INSERT INTO INVENTORY VALUES('EST-7',10000)
INSERT INTO INVENTORY VALUES('EST-8',10000)
INSERT INTO INVENTORY VALUES('EST-9',10000)
INSERT INTO SEQUENCE VALUES('linenum',1000)
INSERT INTO SEQUENCE VALUES('ordernum',1000)