-- ***********************************************************************************************************************************
-- CREATE TABLE script
--
-- copyright 2000 <Gen-it>
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- When:   2/15/2000 
-- Who:    <Gen-it> 
-- What:   Initial release generated with <Gen-it> SQLCreate.xsl v1.0
--************************************************************************************************************************************

--************************************************************************************************************************************
-- Drop Constraints
--************************************************************************************************************************************

ALTER TABLE T_Orderline DROP CONSTRAINT "FK_Orderline_productid"
ALTER TABLE T_Orderline DROP CONSTRAINT "FK_Orderline_ordertotalid"
ALTER TABLE T_OrderTotal DROP CONSTRAINT "FK_OrderTotal_customerid"

--************************************************************************************************************************************
-- Drop Tables
--************************************************************************************************************************************

DROP TABLE T_Customer;
DROP TABLE T_Orderline;
DROP TABLE T_Ordertotal;
DROP TABLE T_Product;

--************************************************************************************************************************************
-- Create Tables
--************************************************************************************************************************************

CREATE TABLE T_Customer
(
	customerid                    NUMERIC(10,0)        NOT NULL,
	firstname                     VARCHAR(30)          NULL,
	lastname                      VARCHAR(50)          NULL,
	company                       VARCHAR(60)          NULL,
	address                       VARCHAR(100)         NOT NULL,
	city                          VARCHAR(50)          NOT NULL,
	zipcode                       VARCHAR(20)          NULL,
	state                         VARCHAR(2)           NULL,
	country                       VARCHAR(30)          NULL,
	CONSTRAINT PK_Customer PRIMARY KEY (customerid)
);

CREATE TABLE T_Orderline
(
	orderlineid                   NUMERIC(10,0)        NOT NULL,
	amount                        NUMERIC(5,0)         NOT NULL,
	price                         NUMERIC(9,2)         NOT NULL,
	productid                     NUMERIC(10,0)        NOT NULL,
	ordertotalid                  NUMERIC(10,0)        NOT NULL,
	CONSTRAINT PK_Orderline PRIMARY KEY (orderlineid)
);

CREATE TABLE T_Ordertotal
(
	ordertotalid                  NUMERIC(10,0)        NOT NULL,
	totalprice                    NUMERIC(9,2)         NOT NULL,
	orderdate                     DATETIME             NOT NULL,
	remarks                       TEXT                 NULL,
	customerid                    NUMERIC(10,0)        NOT NULL,
	CONSTRAINT PK_OrderTotal PRIMARY KEY (ordertotalid)
);

CREATE TABLE T_Product
(
	productid                     NUMERIC(10,0)        NOT NULL,
	productname                   VARCHAR(100)         NOT NULL,
	description                   TEXT                 NULL,
	price                         NUMERIC(9,2)         NOT NULL,
	CONSTRAINT PK_Product PRIMARY KEY (productid)
);


--************************************************************************************************************************************
-- Create Constraints
--************************************************************************************************************************************

ALTER TABLE T_Orderline ADD CONSTRAINT "FK_Orderline_productid"
FOREIGN KEY ("productid") REFERENCES T_Product ("productid");
		
ALTER TABLE T_Orderline ADD CONSTRAINT "FK_Orderline_ordertotalid"
FOREIGN KEY ("ordertotalid") REFERENCES T_OrderTotal ("ordertotalid");
		
ALTER TABLE T_OrderTotal ADD CONSTRAINT "FK_OrderTotal_customerid"
FOREIGN KEY ("customerid") REFERENCES T_Customer ("customerid");