CREATE TABLE City (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL, Region_ID int NOT NULL, Time_Zone_ID int NOT NULL, Zip_Code int NULL, PRIMARY KEY (ID), CONSTRAINT UK_City UNIQUE (Name, Region_ID))

CREATE TABLE Country (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, PRIMARY KEY (ID))

CREATE TABLE Currency (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Symbol varchar(5) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Customer (ID int IDENTITY NOT NULL, City_ID int NOT NULL, Code varchar(25) NOT NULL, Phone varchar(50) NOT NULL UNIQUE, 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, Is_Registered_Online varchar(1) NOT NULL, Email varchar(50) NULL UNIQUE, Username varchar(50) NULL UNIQUE, Password binary(255) NULL, Credit decimal(14, 2) NULL, Created_Emp_Login_ID int NOT NULL, Created_At_Shop_ID int NOT NULL, Created_Time datetime NOT NULL, Last_Login_Time datetime NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Employee (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Work_Commission int NULL, Phone varchar(50) NOT NULL UNIQUE, Email varchar(50) NOT NULL UNIQUE, Username varchar(50) NOT NULL UNIQUE, Password binary(255) NOT NULL, Created_Date datetime NOT NULL, Last_Login_Date datetime NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Employee_Login (ID int IDENTITY NOT NULL, Employee_ID int NOT NULL, Employee_Role_ID int NOT NULL, Login_Time datetime NOT NULL, Logout_Time datetime NULL, Device_IP varchar(50) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Employee_Role (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, Employee_ID int NOT NULL, Role_Type_ID int NOT NULL, Workplace_ID int NOT NULL, Assigned_Date datetime NOT NULL, Revoked_Date datetime NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Function_Type (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Language (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Order_Header (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, Vehicle_ID int NOT NULL, Invoiced_Customer_ID int NOT NULL, Contact_Customer_ID int NOT NULL, Created_Emp_Login_ID int NOT NULL, Created_Time datetime NOT NULL, Submitted_Emp_Login_ID int NULL, Approved_Emp_Login_ID int NULL, Managed_Emp_Login_ID int NULL, Order_No varchar(50) NOT NULL UNIQUE, Complaint_Specifications varchar(255) NOT NULL, Customer_Is_Waiting varchar(1) NOT NULL, Customer_Notes varchar(255) NULL, Start_Time datetime NOT NULL, Due_Time datetime NOT NULL, Vehicle_Arrival_Time datetime NOT NULL, Is_Vehicle_Towed varchar(1) NOT NULL, Odometer_Value int NULL, Odometer_Time datetime NULL, Price_Before_Tax decimal(14, 2) NOT NULL, Total_Tax_Value decimal(14, 2) NOT NULL, Price_After_Tax decimal(14, 2) NOT NULL, Price_Before_Discount decimal(14, 2) NOT NULL, Order_Items_Discount decimal(14, 2) NOT NULL, Order_Discount decimal(14, 2) NOT NULL, Total_Discount_Value decimal(14, 2) NOT NULL, Price_After_Discount decimal(14, 2) NOT NULL, Price_Adjustment decimal(14, 2) NULL, Price_Adjustment_Reason varchar(255) NULL, Price decimal(14, 2) NOT NULL, Latest_Status varchar(50) NOT NULL, Latest_Status_Update datetime NOT NULL, Is_Submitted varchar(1) NOT NULL, Submitted_Time datetime NULL, Is_Approved varchar(1) NOT NULL, Approved_Time datetime NULL, Is_Canceled varchar(1) NOT NULL, Canceled_Time datetime NULL, Cancel_Reason varchar(255) NULL, Is_Ready varchar(1) NOT NULL, Ready_Time datetime NULL, Is_Paid varchar(1) NOT NULL, Payment_Time datetime NULL, Is_Completed varchar(1) NOT NULL, Completed_Time datetime NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Order_History (ID int IDENTITY NOT NULL, Order_ID int NOT NULL, Order_Status_ID int NOT NULL, Start_Time datetime NOT NULL, End_Time datetime NULL, PRIMARY KEY (ID))

CREATE TABLE Order_Line_Cause (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Order_Line_History (ID int IDENTITY NOT NULL, Order_Line_ID int NOT NULL, Order_Line_Status_ID int NOT NULL, Start_Time datetime NOT NULL, End_Time datetime NULL, PRIMARY KEY (ID))

CREATE TABLE Order_Line_Status (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Order_Status (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Organization (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, Is_Inventory varchar(1) NOT NULL, Is_Labor varchar(1) NOT NULL, PRIMARY KEY (ID))

CREATE TABLE Region (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL, Country_ID int NOT NULL, PRIMARY KEY (ID), CONSTRAINT UK_Region UNIQUE (Name, Country_ID))

CREATE TABLE Role_Function (ID int IDENTITY NOT NULL, Role_Type_ID int NOT NULL, Function_Type_ID int NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID), CONSTRAINT UK_Role_Function UNIQUE (Role_Type_ID, Function_Type_ID))

CREATE TABLE Role_Type (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Shop (ID int IDENTITY NOT NULL, City_ID int NOT NULL, Language_ID int NOT NULL, Currency_ID int NOT NULL, Admin_User_ID int NOT NULL, Code varchar(25) NULL UNIQUE, Name varchar(50) NOT NULL UNIQUE, Is_Active varchar(1) NOT NULL, Legal_Entity_Name varchar(255) NOT NULL, Tax_Code varchar(50) NOT NULL, Address varchar(255) NOT NULL, Registration_Number varchar(50) NOT NULL, GPS_Location varchar(50) NULL, Postal_Code varchar(50) NULL, Phone varchar(50) NULL, Fax varchar(50) NULL, Email varchar(50) NULL, Website varchar(255) NULL, Logo image NULL, Bank_Branch varchar(255) NULL, Bank_Code varchar(50) NULL, Bank_Account varchar(50) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Tax_Type (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, Name varchar(50) NOT NULL UNIQUE, Code varchar(50) NOT NULL, Description varchar(255) NULL, Is_Percentage varchar(1) NOT NULL, Value decimal(14, 2) NOT NULL, Created_Time datetime NOT NULL, Start_Time datetime NOT NULL, End_Time datetime NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Time_Zone (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

CREATE TABLE Vehicle (ID int IDENTITY NOT NULL, Vehicle_Model_ID int NOT NULL, Owner_ID int NOT NULL, Contact_Person_ID int NOT NULL, License_Plate varchar(50) NOT NULL, VIN varchar(50) NOT NULL UNIQUE, Engine_Serial_No varchar(50) NOT NULL UNIQUE, Manufactured_Year int NULL, Manufactured_Month int NULL, Registration_Doc varchar(50) NULL, Last_Odometer_Value int NULL, Last_Odometer_Time datetime NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Workplace (ID int IDENTITY NOT NULL, Shop_ID int NOT NULL, Workplace_Type_ID int NOT NULL, Inventory_Organization_ID int NOT NULL, Labor_Organization_ID int NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, Comments varchar(1000) NULL, PRIMARY KEY (ID))

CREATE TABLE Workplace_Type (ID int IDENTITY NOT NULL, Name varchar(50) NOT NULL UNIQUE, Description varchar(255) NULL, PRIMARY KEY (ID))

ALTER TABLE City ADD CONSTRAINT FK_City_Region FOREIGN KEY (Region_ID) REFERENCES Region (ID)

ALTER TABLE City ADD CONSTRAINT FK_City_Time_Zone FOREIGN KEY (Time_Zone_ID) REFERENCES Time_Zone (ID)

ALTER TABLE Customer ADD CONSTRAINT FK_Customer_City FOREIGN KEY (City_ID) REFERENCES City (ID)

ALTER TABLE Customer ADD CONSTRAINT FK_Customer_Emp_Created FOREIGN KEY (Created_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Customer ADD CONSTRAINT FK_Customer_Shop FOREIGN KEY (Created_At_Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Employee_Login ADD CONSTRAINT FK_Employee_Login FOREIGN KEY (Employee_ID) REFERENCES Employee (ID)

ALTER TABLE Employee_Login ADD CONSTRAINT FK_Employee_Login_Role FOREIGN KEY (Employee_Role_ID) REFERENCES Employee_Role (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role FOREIGN KEY (Employee_ID) REFERENCES Employee (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role_Role_Type FOREIGN KEY (Role_Type_ID) REFERENCES Role_Type (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role_Shop FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Employee_Role ADD CONSTRAINT FK_Employee_Role_Workplace FOREIGN KEY (Workplace_ID) REFERENCES Workplace (ID)

ALTER TABLE Employee ADD CONSTRAINT FK_Employee_Shop FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Contact_Customer FOREIGN KEY (Contact_Customer_ID) REFERENCES Customer (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Emp_Order_Approved FOREIGN KEY (Approved_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Emp_Order_Created FOREIGN KEY (Created_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Emp_Order_Managed FOREIGN KEY (Managed_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Emp_Order_Submitted FOREIGN KEY (Submitted_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Header_Shop FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Order_History ADD CONSTRAINT FK_Order_History_Order_Header FOREIGN KEY (Order_ID) REFERENCES Order_Header (ID)

ALTER TABLE Order_History ADD CONSTRAINT FK_Order_History_Order_Status FOREIGN KEY (Order_Status_ID) REFERENCES Order_Status (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Invoiced_Customer FOREIGN KEY (Invoiced_Customer_ID) REFERENCES Customer (ID)

ALTER TABLE Order_Line_History ADD CONSTRAINT FK_Order_Line_History_Status FOREIGN KEY (Order_Line_Status_ID) REFERENCES Order_Line_Status (ID)

ALTER TABLE Order_Line ADD CONSTRAINT FK_Order_Line_Submitted_Emp FOREIGN KEY (Submitted_Emp_Login_ID) REFERENCES Employee_Login (ID)

ALTER TABLE Order_Header ADD CONSTRAINT FK_Order_Vehicle FOREIGN KEY (Vehicle_ID) REFERENCES Vehicle (ID)

ALTER TABLE Organization ADD CONSTRAINT FK_Organization_Shop FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Region ADD CONSTRAINT FK_Region_Country FOREIGN KEY (Country_ID) REFERENCES Country (ID)

ALTER TABLE Role_Function ADD CONSTRAINT FK_Role_Function_Function_Type FOREIGN KEY (Function_Type_ID) REFERENCES Function_Type (ID)

ALTER TABLE Role_Function ADD CONSTRAINT FK_Role_Function_Role_Type FOREIGN KEY (Role_Type_ID) REFERENCES Role_Type (ID)

ALTER TABLE Shop ADD CONSTRAINT FK_Shop_City FOREIGN KEY (City_ID) REFERENCES City (ID)

ALTER TABLE Shop ADD CONSTRAINT FK_Shop_Currency FOREIGN KEY (Currency_ID) REFERENCES Currency (ID)

ALTER TABLE Shop ADD CONSTRAINT FK_Shop_Language FOREIGN KEY (Language_ID) REFERENCES Language (ID)

ALTER TABLE Workplace ADD CONSTRAINT FK_Shop_Workplace FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Tax_Type ADD CONSTRAINT FK_Tax_Type_Shop FOREIGN KEY (Shop_ID) REFERENCES Shop (ID)

ALTER TABLE Vehicle ADD CONSTRAINT FK_Vehicle_Contact_Person FOREIGN KEY (Contact_Person_ID) REFERENCES Customer (ID)

ALTER TABLE Vehicle ADD CONSTRAINT FK_Vehicle_Owner FOREIGN KEY (Owner_ID) REFERENCES Customer (ID)

ALTER TABLE Workplace ADD CONSTRAINT FK_Workplace_Inventory FOREIGN KEY (Inventory_Organization_ID) REFERENCES Organization (ID)

ALTER TABLE Workplace ADD CONSTRAINT FK_Workplace_Labor FOREIGN KEY (Labor_Organization_ID) REFERENCES Organization (ID)

ALTER TABLE Workplace ADD CONSTRAINT FK_Workplace_Type FOREIGN KEY (Workplace_Type_ID) REFERENCES Workplace_Type (ID)