CREATE TABLE Document (ID int IDENTITY NOT NULL, Store_ID int NOT NULL, Document_Type_ID int NOT NULL, Number varchar(25) NOT NULL, External_Number varchar(25) NULL, Order_Header_ID int NULL, Sale_Return_ID int NULL, Purchase_ID int NULL, Stock_Return_ID int NULL, Inventory_Count_ID int NULL, Loss_And_Damage_ID int NULL, Created_Time timestamp NOT NULL, Created_Emp_Login_ID int NOT NULL, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Document_Category (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Document_Type (ID int IDENTITY NOT NULL, Document_Category_ID int NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Inventory_Count (ID int IDENTITY NOT NULL, Store_ID int NOT NULL, Physical_Count_Time timestamp NOT NULL, Change_Stock_Time int NULL, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Inventory_Count_Item (ID int IDENTITY NOT NULL, Inventory_Count_ID int NOT NULL, Item_ID int NOT NULL, Quantity_Counted int NOT NULL, Quantity_Expected int NOT NULL, Quantity_Change int NULL, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

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

CREATE TABLE Purchase (ID int IDENTITY NOT NULL, Store_ID int NOT NULL, Supplier_ID int NOT NULL, Entry_Stock_Time timestamp NOT NULL, Description varchar(255) NULL, Is_Paid varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Purchase_Item (ID int IDENTITY NOT NULL, Purchase_ID int NOT NULL, Item_ID int NOT NULL, Line_No varchar(50) NOT NULL, Quantity int NOT NULL, Supplier_Price_Before_Tax decimal(15, 3) NOT NULL, Supplier_Tax_Value decimal(15, 3) NOT NULL, Supplier_Price_After_Tax decimal(15, 3) NOT NULL, Supplier_Discount_Value decimal(15, 3) NOT NULL, Supplier_Price decimal(15, 3) NOT NULL, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Stock_History (ID int IDENTITY NOT NULL, Store_ID int NOT NULL, Item_ID int NOT NULL, Document_ID int NOT NULL, Initial_Stock_Quantity int NOT NULL, Initial_Item_Cost decimal(15, 3) NOT NULL, Is_Stock_Entry varchar(1) NOT NULL, Quantity int NOT NULL, Current_Stock_Quantity int NOT NULL, Current_Item_Cost decimal(15, 3) NOT NULL, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Supplier (ID int IDENTITY NOT NULL, Store_ID int NOT NULL, City_ID int NOT NULL, Code varchar(10) NOT NULL UNIQUE, Phone varchar(50) NOT NULL, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Is_Company varchar(1) NOT NULL, Company_Name varchar(255) NULL, Tax_Number varchar(50) NULL, Is_Tax_Exempted varchar(1) NOT NULL, Billing_Address varchar(255) NOT NULL, Postal_Code varchar(50) NULL, Email varchar(50) NOT NULL, Created_Emp_Login_ID int NOT NULL, Created_Time timestamp NOT NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, 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)