CREATE TABLE Directors (

	Id INT IDENTITY (1,1) NOT NULL,
	DirectorName  NVARCHAR(50)NOT NULL,
	Notes NVARCHAR(200)NOT NULL,
	CONSTRAINT PK_Directors PRIMARY KEY (Id)
)

CREATE TABLE Categories   (

	Id INT IDENTITY (1,1) NOT NULL,
	CategoryName  NVARCHAR(50)NOT NULL,
	DailyRate FLOAT(2) NOT NULL,
	WeeklyRate FLOAT(2) NOT NULL,
	MonthlyRate FLOAT(2) NOT NULL,
	WeekendRate FLOAT(2) NOT NULL,

	CONSTRAINT PK_Categories PRIMARY KEY (Id)
)
CREATE TABLE Cars   (

	Id INT IDENTITY (1,1) NOT NULL,
	PlateNumber NVARCHAR(50)NOT NULL,
	Manufacturer NVARCHAR(50)NOT NULL,
	Model NVARCHAR(50)NOT NULL,
	CarYear DATETIME NOT NULL,
	CategoryId INT NOT NULL,
	Doors INT NOT NULL,
	Picture VARBINARY(2000),
	Condition NVARCHAR(25) NOT NULL,
	Available BIT NOT NULL

	CONSTRAINT PK_Car PRIMARY KEY (Id)
)

CREATE TABLE Employees     (

	Id INT IDENTITY (1,1) NOT NULL,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	Title NVARCHAR(50) NOT NULL,
	Notes NVARCHAR(200),
	CONSTRAINT PK_Employees PRIMARY KEY (Id)
)
CREATE TABLE Customers      (

	Id INT IDENTITY (1,1) NOT NULL,
	DriverLicenceNumber NVARCHAR(50) NOT NULL,
	FullName NVARCHAR(50) NOT NULL,
	[Address] NVARCHAR(50) NOT NULL,
	City NVARCHAR(50) NOT NULL,
	ZIPCode NVARCHAR(50) NOT NULL,
	Notes NVARCHAR(200) NOT NULL


	CONSTRAINT PK_Customers PRIMARY KEY (Id)
)
CREATE TABLE 	RentalOrders       (

	Id INT IDENTITY (1,1) NOT NULL,
	EmployeeId INT NOT NULL,
	CustomerId INT NOT NULL,
	CarId INT NOT NULL,
	TankLevel FLOAT(2),
	KilometrageStart INT NOT NULL,
	KilometrageEnd INT NOT NULL,
	TotalKilometrage INT NOT NULL,
	StartDate DATETIME NOT NULL,
	EndDate DATETIME NOT NULL,
	TotalDays INT NOT NULL,
	RateApplied FLOAT(2) NOT NULL,
	TaxRate FLOAT(2) NOT NULL,
	OrderStatus NVARCHAR(200) NOT NULL,
	Notes NVARCHAR(200) NOT NULL


	CONSTRAINT PK_RentalOrders  PRIMARY KEY (Id)
)
ALTER TABLE RentalOrders
ADD CONSTRAINT fk_RentalOrders_Employees FOREIGN KEY(EmployeeId) REFERENCES Employees(Id);

ALTER TABLE RentalOrders
ADD CONSTRAINT fk_RentalOrders_Customer FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

ALTER TABLE RentalOrders
ADD CONSTRAINT fk_RentalOrders_Car FOREIGN KEY(CarId) REFERENCES Cars(Id);
ALTER TABLE Cars
ADD CONSTRAINT fk_Cars_Category FOREIGN KEY(CategoryId) REFERENCES Categories(Id);

INSERT INTO Categories(CategoryName, DailyRate, WeeklyRate, MonthlyRate, WeekendRate) VALUES
('Crimi', 2.44, 3.55, 5.33, 10.22),
('Adventure', 5.44, 2.55, 8.33, 11.22),
('Action', 8.44, 13.55, 25.33, 13.22);

INSERT INTO Cars(PlateNumber, Manufacturer, Model, CarYear, CategoryId, Doors, Picture, Condition, Available) VALUES
('Some Number', 'Some text', 'BMW', '2009-05-12', 1, 4, NULL, 'Some text', 1),
('Some Number', 'Some text', 'Mercedes', '2008-06-10', 2, 3, NULL, 'Some text', 1),
('Some Number', 'Some text', 'VW', '2007-01-08', 3, 5, NULL, 'Some text', 0);

INSERT INTO Employees(FirstName, LastName, Title, Notes) VALUES
('Georgi', 'Stalev', 'seller', 'Some text'),
('Ivan', 'Stalev', 'customer suport', 'Some text'),
('Aneta', 'Moleva', 'human resurces', 'Some text');

INSERT INTO Customers(DriverLicenceNumber, FullName, [Address], City, ZIPCode, Notes) VALUES
('Some Number', 'Vladislav Boichev', 'Some address', 'Some city', 'Some code', 'Some text'),
('Some Number', 'Dimitar Vasilev', 'Some address', 'Some city', 'Some code', 'Some text'),
('Some Number', 'Yanislav Marinov', 'Some address', 'Some city', 'Some code', 'Some text');

INSERT INTO RentalOrders(EmployeeId, CustomerId, CarId, TankLevel, KilometrageStart, KilometrageEnd, TotalKilometrage, StartDate, EndDate, TotalDays, RateApplied, TaxRate, OrderStatus, Notes) VALUES
(1, 1, 1, 25.2, 12, 10, 200, '2016-05-05', '2017-05-05', 25, 24.45, 25.44, 'Available', 'Some text'),
(2, 2, 2, 25.2, 3, 5, 150, '2014-05-05', '2015-05-05', 45, 55.22, 33.33, 'Available', 'Some text'),
(3, 3, 3, 25.2, 14, 20, 100, '2015-05-05', '2016-05-05', 26, 27.49, 21.42, 'Available', 'Some text');