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

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

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

CREATE TABLE Customer (ID int(10) NOT NULL AUTO_INCREMENT, City_ID int(10) 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), Tax_Number varchar(50), Is_Tax_Exempted varchar(1) NOT NULL, Billing_Address varchar(255) NOT NULL, Is_Registered_Online varchar(1) NOT NULL, Email varchar(50) UNIQUE, Username varchar(50) UNIQUE, Password binary(255), Credit decimal(14, 2), Created_Emp_Login_ID int(10) NOT NULL, Created_At_Shop_ID int(10) NOT NULL, Created_Time timestamp NOT NULL, Last_Login_Time timestamp NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Employee (ID int(10) NOT NULL AUTO_INCREMENT, Shop_ID int(10) NOT NULL, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Work_Commission int(3), 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 timestamp NOT NULL, Last_Login_Date timestamp NULL, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

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

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

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

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

CREATE TABLE Order_Header (ID int(10) NOT NULL AUTO_INCREMENT, Shop_ID int(10) NOT NULL, Vehicle_ID int(10) NOT NULL, Invoiced_Customer_ID int(10) NOT NULL, Contact_Customer_ID int(10) NOT NULL, Created_Emp_Login_ID int(10) NOT NULL, Created_Time timestamp NOT NULL, Submitted_Emp_Login_ID int(10), Approved_Emp_Login_ID int(10), Managed_Emp_Login_ID int(10), Order_No varchar(50) NOT NULL UNIQUE, Complaint_Specifications varchar(255) NOT NULL, Customer_Is_Waiting varchar(1) NOT NULL, Customer_Notes varchar(255), Start_Time timestamp NOT NULL, Due_Time timestamp NOT NULL, Vehicle_Arrival_Time timestamp NOT NULL, Is_Vehicle_Towed varchar(1) NOT NULL, Odometer_Value int(10), Odometer_Time timestamp 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), Price_Adjustment_Reason varchar(255), Price decimal(14, 2) NOT NULL, Latest_Status varchar(50) NOT NULL, Latest_Status_Update timestamp NOT NULL, Is_Submitted varchar(1) NOT NULL, Submitted_Time timestamp NULL, Is_Approved varchar(1) NOT NULL, Approved_Time timestamp NULL, Is_Canceled varchar(1) NOT NULL, Canceled_Time timestamp NULL, Cancel_Reason varchar(255), Is_Ready varchar(1) NOT NULL, Ready_Time timestamp NULL, Is_Paid varchar(1) NOT NULL, Payment_Time timestamp NULL, Is_Completed varchar(1) NOT NULL, Completed_Time timestamp NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Order_History (ID int(10) NOT NULL AUTO_INCREMENT, Order_ID int(10) NOT NULL, Order_Status_ID int(10) NOT NULL, Start_Time timestamp NOT NULL, End_Time timestamp NULL, PRIMARY KEY (ID))

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

CREATE TABLE Order_Line_History (ID int(10) NOT NULL AUTO_INCREMENT, Order_Line_ID int(10) NOT NULL, Order_Line_Status_ID int(10) NOT NULL, Start_Time timestamp NOT NULL, End_Time timestamp NULL, PRIMARY KEY (ID))

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

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

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

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

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

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

CREATE TABLE Shop (ID int(10) NOT NULL AUTO_INCREMENT, City_ID int(10) NOT NULL, Language_ID int(10) NOT NULL, Currency_ID int(10) NOT NULL, Admin_User_ID int(10) NOT NULL, Code varchar(25) 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), Postal_Code varchar(50), Phone varchar(50), Fax varchar(50), Email varchar(50), Website varchar(255), Logo blob, Bank_Branch varchar(255), Bank_Code varchar(50), Bank_Account varchar(50), Comments varchar(1000), PRIMARY KEY (ID))

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

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

CREATE TABLE Vehicle (ID int(10) NOT NULL AUTO_INCREMENT, Vehicle_Model_ID int(10) NOT NULL, Owner_ID int(10) NOT NULL, Contact_Person_ID int(10) 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(4), Manufactured_Month int(2), Registration_Doc varchar(50), Last_Odometer_Value int(10), Last_Odometer_Time timestamp NULL, Comments varchar(1000), PRIMARY KEY (ID))

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

CREATE TABLE Workplace_Type (ID int(10) NOT NULL AUTO_INCREMENT, Name varchar(50) NOT NULL UNIQUE, Description varchar(255), 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)