-- 1. Catagories
DROP TABLE Categories CASCADE CONSTRAINTS;
CREATE TABLE Categories
(
CategoryID NUMBER(8,0) NOT NULL,
CategoryName VARCHAR2(15) NOT NULL,
Description VARCHAR2(300),
CategoryCode NUMBER(6,0),
CONSTRAINT Categories_CategoryID_pk PRIMARY KEY(CategoryID),
CONSTRAINT Categories_CategoryCode_UK UNIQUE (CategoryCode)
);

--2. Customers
DROP TABLE Customers CASCADE CONSTRAINTS;
CREATE TABLE Customers
(
CustomerID CHAR(8) NOT NULL,
CompanyName VARCHAR2(40) NOT NULL,
ContactName VARCHAR2(30),
ContactTitle VARCHAR2(30),
Address VARCHAR2(60),
City VARCHAR2(15),
Region VARCHAR2(15),
PostalCode VARCHAR2(10),
Country VARCHAR2(15),
Phone VARCHAR2(24),
Fax VARCHAR2(24),
Email VARCHAR2(50),
CONSTRAINT Customer_CustomerID_pk PRIMARY KEY(CustomerID),
CONSTRAINT Customer_Email_UK UNIQUE (Email)
);

--3. Employees
DROP TABLE Employees CASCADE CONSTRAINTS;
CREATE TABLE Employees
(
EmployeeID NUMBER(8,0) NOT NULL,
LastName VARCHAR(20) NOT NULL,
FirstName VARCHAR(10) NOT NULL,
Title VARCHAR(30),
TitleOfCourtesy VARCHAR(25),
BirthDate DATE,
HireDate DATE,
Address VARCHAR(60),
City VARCHAR(15),
Region VARCHAR(15),
PostalCode VARCHAR(10),
Country VARCHAR(15),
HomePhone VARCHAR(24),
Extension VARCHAR(24),
Notes VARCHAR(600),
Reportsto NUMBER(8,0),
PhotoPath VARCHAR(255),
SIN CHAR(9),
CONSTRAINT Employees_EmployeeID_pk PRIMARY KEY(EmployeeID),
CONSTRAINT Employees_SIN_UK UNIQUE (SIN)
);

--7. Suppliers
DROP TABLE Suppliers CASCADE CONSTRAINTS;
CREATE TABLE Suppliers
(
SupplierID NUMBER(8,0) NOT NULL,
CompanyName VARCHAR2(40) NOT NULL,
ContactName VARCHAR2(30),
ContactTitle VARCHAR2(30),
Address VARCHAR2(60),
City VARCHAR2(15),
Region VARCHAR2(15),
PostalCode VARCHAR2(10),
Country VARCHAR2(15),
Phone VARCHAR2(24),
Fax VARCHAR2(24),
HomePage VARCHAR2(200),
CONSTRAINT Suppliers_SupplierID_pk PRIMARY KEY(SupplierID)
);

--8. Shippers
DROP TABLE Shippers CASCADE CONSTRAINTS;
CREATE TABLE Shippers
(
ShipperID NUMBER(8,0) NOT NULL,
CompanyName VARCHAR2(40) NOT NULL,
Phone VARCHAR2(24),
CONSTRAINT Shippers_ShippersID_pk PRIMARY KEY(ShipperID)
);

--5. Orders
DROP TABLE Orders CASCADE CONSTRAINTS;
CREATE TABLE Orders
(
OrderID NUMBER(8,0) NOT NULL,
CustomerID CHAR(8),
EmployeeID NUMBER(8,0),
TerritoryID VARCHAR2(20),
OrderDate DATE,
RequiredDate DATE,
ShippedDate DATE,
ShipVia NUMBER(8,0),
Freight NUMBER(8,2),
ShipName VARCHAR2(40),
ShipAddress VARCHAR2(60),
ShipCity VARCHAR2(15),
ShipRegion VARCHAR2(15),
ShipPostalCode VARCHAR2(10),
ShipCountry VARCHAR2(15),
CONSTRAINT Orders_OrderID_pk PRIMARY KEY(OrderID),
CONSTRAINT Orders_Customers_fk FOREIGN KEY(CustomerID) REFERENCES Customers(CustomerID),
CONSTRAINT Orders_Employees_fk FOREIGN KEY(EmployeeID) REFERENCES Employees(EmployeeID),
CONSTRAINT Orders_Shippers_fk FOREIGN KEY(ShipVia) REFERENCES Shippers(ShipperID)
);

--6. Products
DROP TABLE Products CASCADE CONSTRAINTS;
CREATE TABLE Products
(
ProductID NUMBER(8,0) NOT NULL,
ProductName VARCHAR2(40) NOT NULL,
SupplierID NUMBER(8,0),
CategoryID NUMBER(8,0),
QuantityPerUnit VARCHAR2(20),
UnitPrice NUMBER(8,2),
UnitsInStock NUMBER(6,0),
UnitsOnOrder NUMBER(6,0),
ReorderLevel NUMBER(6,0),
Discontinued NUMBER(1,0) NOT NULL,
CONSTRAINT Products_ProductID_pk PRIMARY KEY(ProductID),
CONSTRAINT Products_UnitPrice_ck CHECK (UnitPrice >= 0),
CONSTRAINT Products_ReorderLevel_ck CHECK (ReorderLevel >= 0),
CONSTRAINT Products_UnitsInStock_ck CHECK (UnitsInStock >= 0),
CONSTRAINT Products_UnitsOnOrder_ck CHECK (UnitsOnOrder >= 0),
CONSTRAINT Products_Suppliers_fk FOREIGN KEY(SupplierID) REFERENCES Suppliers(SupplierID),
CONSTRAINT Products_Categories_fk FOREIGN KEY(CategoryID) REFERENCES Categories(CategoryID)
);

--4. OrderDetails
DROP TABLE OrderDetails CASCADE CONSTRAINTS;
CREATE TABLE OrderDetails
(
OrderID NUMBER(8,0) NOT NULL,
ProductID NUMBER(8,0) NOT NULL,
UnitPrice NUMBER(8,2) NOT NULL,
Quantity NUMBER(8,0) NOT NULL,
Discount NUMBER(2,2) NOT NULL,
CONSTRAINT OrderDetails_OID_PID_pk PRIMARY KEY(OrderID, ProductID),
CONSTRAINT OrderDetails_Discount_ck CHECK (Discount >= 0 AND Discount <= 1),
CONSTRAINT OrderDetails_Quanitity_ck CHECK (Quantity > 0),
CONSTRAINT OrderDetails_UnitPrice_ck CHECK (UnitPrice >= 0),
CONSTRAINT OrderDetails_Products_fk FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
CONSTRAINT OrderDetails_Orders_fk FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

-- Constraint Counts in Projects
SELECT table_name, constraint_name, constraint_type, search_condition
FROM USER_CONSTRAINTS
WHERE table_name IN ('CATEGORIES','EMPLOYEES', 'ORDERS',
'ORDERDETAILS', 'SHIPPERS', 'SUPPLIERS',
'PRODUCTS', 'CUSTOMERS')
AND constraint_name NOT LIKE 'SYS%'
ORDER BY 1;

--EXEC dbms_stats.gather_schema_stats('Projects');
--SELECT table_name, num_rows, 'Daniel Ndreca' FROM user_tables;