#12
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category VARCHAR(30) NOT NULL,
    daily_rate DECIMAL NOT NULL,
    weekly_rate DECIMAL NOT NULL,
    monthly_rate DECIMAL NOT NULL,
    weekend_rate DECIMAL NOT NULL
);
CREATE TABLE cars (
    id INT PRIMARY KEY AUTO_INCREMENT,
    plate_number VARCHAR(30) NOT NULL,
    make VARCHAR(30) NOT NULL,
    model VARCHAR(30) NOT NULL,
    car_year YEAR NOT NULL,
    daily_rate DECIMAL NOT NULL,
    category_id INT NOT NULL,
    doors INT NOT NULL,
    car_condition VARCHAR(30) NOT NULL,
    available BOOLEAN NOT NULL
);
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    title VARCHAR(30) NOT NULL,
    notes TEXT
);
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    driver_licence_number VARCHAR(30) NOT NULL,
    full_name VARCHAR(30) NOT NULL,
    address VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    zip_code INT NOT NULL,
    notes TEXT
);
CREATE TABLE rental_orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT NOT NULL,
    customer_id INT NOT NULL,
    car_id INT NOT NULL,
    car_condition VARCHAR(30) NOT NULL,
    tank_level VARCHAR(30) NOT NULL,
    kilometrage_start INT NOT NULL,
    kilometrage_end INT NOT NULL,
    total_kilometrage INT NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_days INT NOT NULL,
    rate_applied VARCHAR(30) NOT NULL,
    tax_rate DECIMAL NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    notes TEXT
);

INSERT INTO `categories` VALUES (1, 'Sofia',1,2,3,4),(2, 'Plovdiv',1,2,3,4),(3, 'Varna',1,2,3,4);
INSERT INTO `cars` VALUES (1, '1','1','1','1990',1,1,1,'good',FALSE),(2, '1','1','1','1990',1,1,1,'good',FALSE),(3, '1','1','1','1990',1,1,1,'good',FALSE);
INSERT INTO `employees` VALUES (1, 'Sofia','1','1','1'),(2, 'Sofia','1','1','1'),(3,'Sofia','1','1','1');
INSERT INTO `customers` VALUES (1, 'Sofia','1','1','1',1,'11'),(2, 'Sofia','1','1','1',1,'11'),(3,'Sofia','1','1','1',1,'11');
INSERT INTO `rental_orders` VALUES (1, 1,1,1,'Sofia','1',1,1,1,CURRENT_DATE(),CURRENT_DATE(),1,1,1,'1','111'),(2, 1,1,1,'Sofia','1',1,1,1,CURRENT_DATE(),CURRENT_DATE(),1,1,1,'1','111'),(3, 1,1,1,'Sofia','1',1,1,1,CURRENT_DATE(),CURRENT_DATE(),1,1,1,'1','111');