CREATE TABLE categories
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(50) NOT NULL,
daily_rate DOUBLE NOT NULL,
weekly_rate DOUBLE NOT NULL,
montly_rate DOUBLE NOT NULL,
weekend_rate DOUBLE NOT NULL
);

CREATE TABLE cars
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
plate_number VARCHAR(50) NOT NULL,
make VARCHAR(50) NOT NULL,
model VARCHAR(50) NOT NULL,
car_year VARCHAR(50) NOT NULL,
category_id INT NOT NULL,
doors VARCHAR(50) NOT NULL,
picture BINARY(200) DEFAULT NULL,
car_condition VARCHAR(50) DEFAULT 'missing',
available VARCHAR(50) NOT NULL
);

CREATE TABLE employees
(
id INT AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
title VARCHAR(50) NOT NULL,
notes VARCHAR(50) DEFAULT 'none',
PRIMARY KEY(id)
);

CREATE TABLE customers
(
id INT AUTO_INCREMENT,
driver_licence_number VARCHAR(50) NOT NULL,
full_name VARCHAR(50) NOT NULL,
adress VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
zip_code VARCHAR(50) DEFAULT 'none',
notes VARCHAR(50) DEFAULT 'none',
PRIMARY KEY(id)
);

CREATE TABLE rental_orders
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
employee_id INT NOT NULL,
customer_id INT NOT NULL,
car_id INT NOT NULL,
car_condition VARCHAR(50) DEFAULT 'none',
tank_level VARCHAR(50) DEFAULT 'full',
kilometrage_start VARCHAR(50) NOT NULL,
kilometrage_end VARCHAR(50) NOT NULL,
total_kilometrage VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
total_days VARCHAR(50) NOT NULL,
rate_applied VARCHAR(50) DEFAULT 'none',
tax_rate VARCHAR(50) NOT NULL,
order_status VARCHAR(50) DEFAULT 'none',
notes VARCHAR(50) DEFAULT 'none'
);

ALTER TABLE cars
ADD CONSTRAINT fk_category_id FOREIGN KEY (category_id) REFERENCES categories(id);

ALTER TABLE rental_orders
ADD CONSTRAINT fk_employee_id FOREIGN KEY (employee_id) REFERENCES employees(id),
ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES customers(id),
ADD CONSTRAINT fk_car_id FOREIGN KEY (car_id) REFERENCES cars(id);

INSERT INTO categories (category, daily_rate, weekly_rate, montly_rate, weekend_rate)
VALUES ('premium', 5, 4.5, 6.7, 8.9),
('sedan', 4, 3.5, 5.6, 7.8),
('small', 3, 2.5, 4.5, 5.6);

INSERT INTO cars (plate_number, make, model, car_year, category_id, doors, available)
VALUES ('SA9999SA', 'AUDI', 'A4', '2006', 1, '4', 'yes'),
('SA9991SA', 'AUDI', 'A6', '2008', 2, '4', 'yes'),
('SA9992SA', 'AUDI', 'A8', '2009', 3, '4', 'yes');

INSERT INTO employees (first_name, last_name, title)
VALUES ('Ivan', 'Ivanov', 'specialist'),
('Stoyan', 'Stoyanov', 'izpylnitel'),
('Kolio', 'Koliov', 'obshtak');

INSERT INTO customers (driver_licence_number, full_name, adress, city)
VALUES ('1234', 'Stamat Stamat', 'Mladost', 'Sofia'),
('1224', 'Stamat Stamatov', 'ZonaB5', 'Sofia'),
('1234', 'Stamat Ivanov', 'Mladost4', 'Sofia');

INSERT INTO rental_orders (employee_id, customer_id, car_id, kilometrage_start, kilometrage_end, total_kilometrage, start_date, end_date, total_days, tax_rate)
VALUES (1, 1, 1, '10000', '11000', '11000', '2016-10-01', '2016-10-02', '2', '45'),
(2, 2, 2, '10000', '12000', '12000', '2016-10-01', '2016-10-03', '3', '49'),
(3, 3, 3, '10000', '10500', '10500', '2016-10-01', '2016-10-01', '1', '40');