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

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

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

CREATE TABLE Customer (ID SERIAL NOT NULL, City_ID int4 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 bytea, Credit numeric(14, 2), Created_Emp_Login_ID int4 NOT NULL, Created_At_Shop_ID int4 NOT NULL, Created_Time timestamp NOT NULL, Last_Login_Time timestamp, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Employee (ID SERIAL NOT NULL, Shop_ID int4 NOT NULL, First_Name varchar(50) NOT NULL, Last_Name varchar(50) NOT NULL, Work_Commission int4, Phone varchar(50) NOT NULL UNIQUE, Email varchar(50) NOT NULL UNIQUE, Username varchar(50) NOT NULL UNIQUE, Password bytea NOT NULL, Created_Date timestamp NOT NULL, Last_Login_Date timestamp, Is_Active varchar(1) NOT NULL, Comments varchar(1000), PRIMARY KEY (ID))

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

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

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

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

CREATE TABLE Order_Header (ID SERIAL NOT NULL, Shop_ID int4 NOT NULL, Vehicle_ID int4 NOT NULL, Invoiced_Customer_ID int4 NOT NULL, Contact_Customer_ID int4 NOT NULL, Created_Emp_Login_ID int4 NOT NULL, Created_Time timestamp NOT NULL, Submitted_Emp_Login_ID int4, Approved_Emp_Login_ID int4, Managed_Emp_Login_ID int4, 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 int4, Odometer_Time timestamp, Price_Before_Tax numeric(14, 2) NOT NULL, Total_Tax_Value numeric(14, 2) NOT NULL, Price_After_Tax numeric(14, 2) NOT NULL, Price_Before_Discount numeric(14, 2) NOT NULL, Order_Items_Discount numeric(14, 2) NOT NULL, Order_Discount numeric(14, 2) NOT NULL, Total_Discount_Value numeric(14, 2) NOT NULL, Price_After_Discount numeric(14, 2) NOT NULL, Price_Adjustment numeric(14, 2), Price_Adjustment_Reason varchar(255), Price numeric(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, Is_Approved varchar(1) NOT NULL, Approved_Time timestamp, Is_Canceled varchar(1) NOT NULL, Canceled_Time timestamp, Cancel_Reason varchar(255), Is_Ready varchar(1) NOT NULL, Ready_Time timestamp, Is_Paid varchar(1) NOT NULL, Payment_Time timestamp, Is_Completed varchar(1) NOT NULL, Completed_Time timestamp, Comments varchar(1000), PRIMARY KEY (ID))

CREATE TABLE Order_History (ID SERIAL NOT NULL, Order_ID int4 NOT NULL, Order_Status_ID int4 NOT NULL, Start_Time timestamp NOT NULL, End_Time timestamp, PRIMARY KEY (ID))

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

CREATE TABLE Order_Line_History (ID SERIAL NOT NULL, Order_Line_ID int4 NOT NULL, Order_Line_Status_ID int4 NOT NULL, Start_Time timestamp NOT NULL, End_Time timestamp, PRIMARY KEY (ID))

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

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

CREATE TABLE Organization (ID SERIAL NOT NULL, Shop_ID int4 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 SERIAL NOT NULL, Name varchar(50) NOT NULL, Country_ID int4 NOT NULL, PRIMARY KEY (ID), CONSTRAINT UK_Region UNIQUE (Name, Country_ID))

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

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

CREATE TABLE Shop (ID SERIAL NOT NULL, City_ID int4 NOT NULL, Language_ID int4 NOT NULL, Currency_ID int4 NOT NULL, Admin_User_ID int4 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 bytea, Bank_Branch varchar(255), Bank_Code varchar(50), Bank_Account varchar(50), Comments varchar(1000), PRIMARY KEY (ID))

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

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

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

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

CREATE TABLE Workplace_Type (ID SERIAL NOT NULL, 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)