CREATE TABLE category (
  Category_Id NUMBER(10) NOT NULL,
  Name varchar(45) NOT NULL,
  PRIMARY KEY (Category_Id)
);
CREATE SEQUENCE category_id_seq
  MINVALUE 1
  START WITH 1000127
  INCREMENT BY 1
  CACHE 200;
  
CREATE TABLE subcategory (
  SubCategory_Id NUMBER(10)  NOT NULL ,
  Category_Id NUMBER(10)  NOT NULL,
  Name varchar(45) NOT NULL,
  CONSTRAINT pk_subid  PRIMARY KEY (SubCategory_Id),  
  CONSTRAINT FK_subcategory_1 FOREIGN KEY (Category_Id) REFERENCES category(Category_Id)
) ;
CREATE SEQUENCE subcategory_id_seq
  MINVALUE 1
  START WITH 1000116
  INCREMENT BY 1
  CACHE 200;

CREATE TABLE product (
  Product_Id NUMBER(10)  NOT NULL ,
  Name varchar(45) NOT NULL,
  Featured NUMBER NOT NULL,
  Price decimal(10,0) NOT NULL,
  Available NUMBER NOT NULL,
  Category_Id NUMBER(10) NOT NULL,
  Description varchar2(200) NOT NULL,
  SubCategory_Id NUMBER(10)  NOT NULL,
  Manufacturer varchar(45) NOT NULL,
  CONSTRAINT pk_pid PRIMARY KEY (Product_Id), 
  CONSTRAINT FK_product_1 FOREIGN KEY (Category_Id) REFERENCES category (Category_Id),
  CONSTRAINT FK_product_2 FOREIGN KEY (SubCategory_Id) REFERENCES subcategory (SubCategory_Id)
);
CREATE SEQUENCE product_id_seq
  MINVALUE 1
  START WITH 125
  INCREMENT BY 1
  CACHE 200;

CREATE TABLE  customer (
  Customer_Id NUMBER(10) NOT NULL ,
  First_Name varchar(45) DEFAULT NULL,
  Last_Name varchar(45) DEFAULT NULL,
  User_Name varchar(45) NOT NULL,
  Pass varchar(45) NOT NULL,
  Email_Address varchar(45) NOT NULL,
  Phone_Number varchar(45) DEFAULT NULL,
  Active NUMBER(1) DEFAULT 0 NOT NULL ,
  PRIMARY KEY (Customer_Id)
) ;

CREATE SEQUENCE customer_id_seq
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 200;

   CREATE TABLE address (
  Address_Id NUMBER(10) NOT NULL ,
  FullName varchar(45) NOT NULL,
  Address_1 varchar(200) NOT NULL,
  Address_2 varchar(45) NOT NULL,
  City varchar(30) NOT NULL,
  Zip varchar(45) NOT NULL,
  State varchar(45) NOT NULL,  
  Customer_Id number(10)  NOT NULL,
  country varchar(40) NOT NULL,
   CONSTRAINT PK_addressid PRIMARY KEY (Address_Id),
   CONSTRAINT FK_custid FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id)
);

CREATE TABLE  shippingaddress(
  Shipping_Id number(10)  NOT NULL ,
  Address_1 varchar(200) NOT NULL,
  Address_2 varchar(45) NOT NULL,
  City varchar(30) NOT NULL,
  Zip varchar(45) NOT NULL,
  State varchar(45) NOT NULL,
  FullName varchar(45) NOT NULL,
   Customer_Id number(10)  NOT NULL,
   country varchar(40) not null,
   CONSTRAINT PK_shippingid PRIMARY KEY (Shipping_Id),
   CONSTRAINT FK_Scustid FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id)
);

CREATE SEQUENCE orderitem_id_seq
  MINVALUE 1
  START WITH 2017
  INCREMENT BY 1
  CACHE 200;


CREATE TABLE  orders (
  Order_Id number(20),
  CreatedDate date NOT NULL,
  UpdatedDate date DEFAULT NULL,
  Email_Address varchar(45) NOT NULL,
  Order_Status varchar(45) NOT NULL,
  Order_SubTotal decimal(10,2) NOT NULL,
  Customer_Id number(10)  NOT NULL,
  Shipping_Id number(10)  NOT NULL,
  CONSTRAINT pk_orderid PRIMARY KEY (Order_Id),
  CONSTRAINT FK_orders_1 FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id),
  CONSTRAINT FK_orders_2 FOREIGN KEY (Shipping_Id) REFERENCES shippingaddress (Shipping_Id)
);


CREATE TABLE  orderitem (
  OrderItem_Id number(10)  NOT NULL,
  Name varchar(45) NOT NULL,
  Price decimal(10,2) NOT NULL,
  Quantity number(10) NOT NULL,
  Category_Id number(10)  NOT NULL,
  SubCategory_Id number(10) NOT NULL,
  Order_Id number(20)NOT NULL,
  Product_Id number(10) NOT NULL,
   CONSTRAINT PK_orderitemid PRIMARY KEY (OrderItem_Id),
  CONSTRAINT FK_orderitem_1 FOREIGN KEY (Category_Id) REFERENCES category (Category_Id),
  CONSTRAINT FK_orderitem_2 FOREIGN KEY (SubCategory_Id) REFERENCES subcategory (SubCategory_Id),
  CONSTRAINT FK_orderitem_3 FOREIGN KEY (Order_Id) REFERENCES orders (Order_Id),
  CONSTRAINT FK_orderitem_4 FOREIGN KEY (Product_Id) REFERENCES product (Product_Id)
);


CREATE TABLE payment (
  CC_Number varchar(45) NOT NULL,
  CC_Name varchar(45) NOT NULL,
  CC_CVV number(3)  NOT NULL,
  CC_Expiry_Month number(2)  NOT NULL,
  CC_Expiry_Year number(4)NOT NULL,
  Customer_Id number(10)  NOT NULL,
  CONSTRAINT FK_payment_1 FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id)
);

CREATE TABLE customeractivation (  
   token varchar2(255) NOT NULL,
   expiry_date TIMESTAMP NOT NULL,
   customer_id number(11) NOT NULL,
   CONSTRAINT FK_Custactivation FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id)
);

CREATE TABLE emailresettoken (  
  token varchar2(255) NOT NULL,
  expiry_date TIMESTAMP NOT NULL,
  customer_id number(11) NOT NULL,
  email varchar2(255) NOT NULL,
  CONSTRAINT email_unique UNIQUE (email),  
  CONSTRAINT FK_emailreset FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id)
);

CREATE TABLE customeraddress (
  CustomerAddress_Id number(10) NOT NULL,
  Name varchar(45) NOT NULL,
  Address_Id number(10)  NOT NULL,
  Customer_Id number(10)  NOT NULL,
 
  CONSTRAINT PK_customeraddressid PRIMARY KEY (CustomerAddress_Id),
  CONSTRAINT FK_CustomerAddress_1 FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id),
   CONSTRAINT FK_CustomerAddress_2 FOREIGN KEY (Address_Id) REFERENCES address (Address_Id)
);


CREATE TABLE  emaildetails (
  UserName varchar(45) NOT NULL,
  Password varchar(45) NOT NULL,
  CONSTRAINT PK_emaildetailsid PRIMARY KEY (UserName)
);
CREATE TABLE review (
  Review_Id number(10) NOT NULL,
  Customer_Id number(10) NOT NULL,
  ReviewText varchar(300) NOT NULL,
  SubmittedDate date NOT NULL,
  Product_Id number(10) NOT NULL,
  ReviewTitle varchar2(45) NOT NULL,
  Rating varchar2(10) NOT NULL,
  CONSTRAINT PK_reviewid PRIMARY KEY (Review_Id), 
  CONSTRAINT FK_review_1 FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id),
   CONSTRAINT FK_review_2 FOREIGN KEY (Product_Id) REFERENCES product (Product_Id)

);

CREATE table reviewsummary (
  Summary_Id number(10) NOT NULL,
  Review_ID number(10) NOT NULL,
  Helpful number(1) NOT NULL,
  Not_Helpful number(1) NOT NULL,
   CONSTRAINT PK_sumaryid PRIMARY KEY (Summary_Id), 
  CONSTRAINT FK_ReviewSummary_1 FOREIGN KEY (Review_ID) REFERENCES review (Review_Id)
) ;

CREATE TABLE rating (
  Rating_ID number(10) NOT NULL,
  Rating varchar(10) NOT NULL,
  Review_ID number(10) NOT NULL,
  Product_Id number(10) NOT NULL,
  CONSTRAINT FK_rating_1 FOREIGN KEY (Review_ID) REFERENCES review (Review_ID),
   CONSTRAINT FK_rating_2 FOREIGN KEY (Product_Id) REFERENCES product (Product_Id)
) ;

CREATE table  wishlistdetails (
  Details_Id number(10) NOT NULL,
  Product_Id number(10) NOT NULL,
  Customer_Id number(10) NOT NULL, 
   CONSTRAINT PK_wishlistdetailsid PRIMARY KEY (Details_Id), 
  CONSTRAINT FK_wishlistdetails_2 FOREIGN KEY (Customer_Id) REFERENCES customer (Customer_Id),
   CONSTRAINT FK_wishlistdetails_3 FOREIGN KEY (Product_Id) REFERENCES product (Product_Id)
);
