CREATE TABLE City (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL, Region_ID number(10) NOT NULL, Time_Zone_ID number(10) NOT NULL, Zip_Code number(30), PRIMARY KEY (ID), CONSTRAINT UK_City UNIQUE (Name, Region_ID))

CREATE TABLE Country (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, PRIMARY KEY (ID))

CREATE TABLE Currency (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Symbol varchar2(5) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Customer (ID number(10) GENERATED AS IDENTITY, City_ID number(10) NOT NULL, Code varchar2(25) NOT NULL, Phone varchar2(50) NOT NULL UNIQUE, 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, Is_Registered_Online varchar2(1) NOT NULL, Email varchar2(50) UNIQUE, Username varchar2(50) UNIQUE, Password raw(255), Credit number(14, 2), Created_Emp_Login_ID number(10) NOT NULL, Created_At_Shop_ID number(10) NOT NULL, Created_Time timestamp(0) NOT NULL, Last_Login_Time timestamp(0), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Employee (ID number(10) GENERATED AS IDENTITY, Shop_ID number(10) NOT NULL, First_Name varchar2(50) NOT NULL, Last_Name varchar2(50) NOT NULL, Work_Commission number(3), Phone varchar2(50) NOT NULL UNIQUE, Email varchar2(50) NOT NULL UNIQUE, Username varchar2(50) NOT NULL UNIQUE, Password raw(255) NOT NULL, Created_Date timestamp(0) NOT NULL, Last_Login_Date timestamp(0), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Employee_Login (ID number(10) GENERATED AS IDENTITY, Employee_ID number(10) NOT NULL, Employee_Role_ID number(10) NOT NULL, Login_Time timestamp(0) NOT NULL, Logout_Time timestamp(0), Device_IP varchar2(50) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Employee_Role (ID number(10) GENERATED AS IDENTITY, Shop_ID number(10) NOT NULL, Employee_ID number(10) NOT NULL, Role_Type_ID number(10) NOT NULL, Workplace_ID number(10) NOT NULL, Assigned_Date timestamp(0) NOT NULL, Revoked_Date timestamp(0), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Function_Type (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Language (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

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

CREATE TABLE Order_History (ID number(10) GENERATED AS IDENTITY, Order_ID number(10) NOT NULL, Order_Status_ID number(10) NOT NULL, Start_Time timestamp(0) NOT NULL, End_Time timestamp(0), PRIMARY KEY (ID))

CREATE TABLE Order_Line_Cause (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Order_Line_History (ID number(10) GENERATED AS IDENTITY, Order_Line_ID number(10) NOT NULL, Order_Line_Status_ID number(10) NOT NULL, Start_Time timestamp(0) NOT NULL, End_Time timestamp(0), PRIMARY KEY (ID))

CREATE TABLE Order_Line_Status (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Order_Status (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Organization (ID number(10) GENERATED AS IDENTITY, Shop_ID number(10) NOT NULL, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Is_Inventory varchar2(1) NOT NULL, Is_Labor varchar2(1) NOT NULL, PRIMARY KEY (ID))

CREATE TABLE Region (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL, Country_ID number(10) NOT NULL, PRIMARY KEY (ID), CONSTRAINT UK_Region UNIQUE (Name, Country_ID))

CREATE TABLE Role_Function (ID number(10) GENERATED AS IDENTITY, Role_Type_ID number(10) NOT NULL, Function_Type_ID number(10) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID), CONSTRAINT UK_Role_Function UNIQUE (Role_Type_ID, Function_Type_ID))

CREATE TABLE Role_Type (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

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

CREATE TABLE Tax_Type (ID number(10) GENERATED AS IDENTITY, Shop_ID number(10) NOT NULL, Name varchar2(50) NOT NULL UNIQUE, Code varchar2(50) NOT NULL, Description varchar2(255), Is_Percentage varchar2(1) NOT NULL, Value number(14, 2) NOT NULL, Created_Time timestamp(0) NOT NULL, Start_Time timestamp(0) NOT NULL, End_Time timestamp(0), Is_Active varchar2(1) NOT NULL, Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Time_Zone (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), PRIMARY KEY (ID))

CREATE TABLE Vehicle (ID number(10) GENERATED AS IDENTITY, Vehicle_Model_ID number(10) NOT NULL, Owner_ID number(10) NOT NULL, Contact_Person_ID number(10) NOT NULL, License_Plate varchar2(50) NOT NULL, VIN varchar2(50) NOT NULL UNIQUE, Engine_Serial_No varchar2(50) NOT NULL UNIQUE, Manufactured_Year number(4), Manufactured_Month number(2), Registration_Doc varchar2(50), Last_Odometer_Value number(10), Last_Odometer_Time timestamp(0), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Workplace (ID number(10) GENERATED AS IDENTITY, Shop_ID number(10) NOT NULL, Workplace_Type_ID number(10) NOT NULL, Inventory_Organization_ID number(10) NOT NULL, Labor_Organization_ID number(10) NOT NULL, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(255), Comments varchar2(1000), PRIMARY KEY (ID))

CREATE TABLE Workplace_Type (ID number(10) GENERATED AS IDENTITY, Name varchar2(50) NOT NULL UNIQUE, Description varchar2(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)