CREATE TABLE Document (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Document_Type_ID number(10) NOT NULL, Number varchar2(25) NOT NULL, External_Number varchar2(25), Order_Header_ID number(10), Sale_Return_ID number(10), Purchase_ID number(10), Stock_Return_ID number(10), Inventory_Count_ID number(10), Loss_And_Damage_ID number(10), Created_Time timestamp(0) NOT NULL, Created_Emp_Login_ID number(10) NOT NULL, Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Document_Category (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Document_Type (ID number(10) GENERATED AS IDENTITY, Document_Category_ID number(10) NOT NULL, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Inventory_Count (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Physical_Count_Time timestamp(0) NOT NULL, Change_Stock_Time number(10), Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Inventory_Count_Item (ID number(10) GENERATED AS IDENTITY, Inventory_Count_ID number(10) NOT NULL, Item_ID number(10) NOT NULL, Quantity_Counted number(10) NOT NULL, Quantity_Expected number(10) NOT NULL, Quantity_Change number(10), Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Item (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Item_Category_ID number(10) NOT NULL, Supplier_ID number(10) NOT NULL, Unit_Of_Measure_ID number(10) NOT NULL, SKU_Code varchar2(25) NOT NULL UNIQUE, Name varchar2(50) NOT NULL, Description varchar2(255), Is_Service varchar2(1) NOT NULL, In_Stock varchar2(1) NOT NULL, Using_Default_Quantity varchar2(1) NOT NULL, Default_Quantity number(10), Current_Stock_Quantity number(10) NOT NULL, Preferred_Stock_Quantity number(10) NOT NULL, Min_Stock_Quantity number(10) NOT NULL, Low_Stock_Warning varchar2(1) NOT NULL, Low_Stock_Quantity number(10), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Purchase (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Supplier_ID number(10) NOT NULL, Entry_Stock_Time timestamp(0) NOT NULL, Description varchar2(255), Is_Paid varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Purchase_Item (ID number(10) GENERATED AS IDENTITY, Purchase_ID number(10) NOT NULL, Item_ID number(10) NOT NULL, Line_No varchar2(50) NOT NULL, Quantity number(10) NOT NULL, Supplier_Price_Before_Tax number(15, 3) NOT NULL, Supplier_Tax_Value number(15, 3) NOT NULL, Supplier_Price_After_Tax number(15, 3) NOT NULL, Supplier_Discount_Value number(15, 3) NOT NULL, Supplier_Price number(15, 3) NOT NULL, Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Stock_History (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, Item_ID number(10) NOT NULL, Document_ID number(10) NOT NULL, Initial_Stock_Quantity number(10) NOT NULL, Initial_Item_Cost number(15, 3) NOT NULL, Is_Stock_Entry varchar2(1) NOT NULL, Quantity number(10) NOT NULL, Current_Stock_Quantity number(10) NOT NULL, Current_Item_Cost number(15, 3) NOT NULL, Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Supplier (ID number(10) GENERATED AS IDENTITY, Store_ID number(10) NOT NULL, City_ID number(10) NOT NULL, Code varchar2(10) NOT NULL UNIQUE, Phone varchar2(50) NOT NULL, First_Name varchar2(50) NOT NULL, Last_Name varchar2(50) NOT NULL, Is_Company varchar2(1) NOT NULL, Company_Name varchar2(255), Tax_Number varchar2(50), Is_Tax_Exempted varchar2(1) NOT NULL, Billing_Address varchar2(255) NOT NULL, Postal_Code varchar2(50), Email varchar2(50) NOT NULL, Created_Emp_Login_ID number(10) NOT NULL, Created_Time timestamp(0) NOT NULL, Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

ALTER TABLE Document ADD CONSTRAINT FK_Document_Document_Type FOREIGN KEY (Document_Type_ID) REFERENCES Document_Type (ID)

ALTER TABLE Document ADD CONSTRAINT FK_Document_Inventory_Count FOREIGN KEY (Inventory_Count_ID) REFERENCES Inventory_Count (ID)

ALTER TABLE Document ADD CONSTRAINT FK_Document_Purchase FOREIGN KEY (Purchase_ID) REFERENCES Purchase (ID)

ALTER TABLE Document_Type ADD CONSTRAINT FK_Document_Type_Document_Category FOREIGN KEY (Document_Category_ID) REFERENCES Document_Category (ID)

ALTER TABLE Inventory_Count_Item ADD CONSTRAINT FK_Inventory_Count_Item_Inventory_Count FOREIGN KEY (Inventory_Count_ID) REFERENCES Inventory_Count (ID)

ALTER TABLE Inventory_Count_Item ADD CONSTRAINT FK_Inventory_Count_Item_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)

ALTER TABLE Item ADD CONSTRAINT FK_Item_Supplier FOREIGN KEY (Supplier_ID) REFERENCES Supplier (ID)

ALTER TABLE Purchase_Item ADD CONSTRAINT FK_Purchase_Item_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)

ALTER TABLE Purchase_Item ADD CONSTRAINT FK_Purchase_Item_Purchase FOREIGN KEY (Purchase_ID) REFERENCES Purchase (ID)

ALTER TABLE Purchase ADD CONSTRAINT FK_Purchase_Supplier FOREIGN KEY (Supplier_ID) REFERENCES Supplier (ID)

ALTER TABLE Stock_History ADD CONSTRAINT FK_Stock_History_Document FOREIGN KEY (Document_ID) REFERENCES Document (ID)

ALTER TABLE Stock_History ADD CONSTRAINT FK_Stock_History_Item FOREIGN KEY (Item_ID) REFERENCES Item (ID)