Car Rental

CREATE DATABASE `car_rental`;

USE `car_rental`;

CREATE TABLE IF NOT EXISTS `categories`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `category` VARCHAR(50) NOT NULL,
    `daily_rate` DECIMAL,
    `weekly_rate` DECIMAL,
    `monthly_rate` DECIMAL,
    `weekend_rate` DECIMAL
);

INSERT INTO `categories` (`category`)
    VALUES ('Van'),
        ('Sedan'),
        ('4x4');

CREATE TABLE IF NOT EXISTS `cars`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `plate_number` VARCHAR(50) NOT NULL UNIQUE,
    `make` VARCHAR(50),
    `model` VARCHAR(50),
    `car_year` INT(4),
    `category_id` INT,
    `doors` INT(1),
    `picture` LONGBLOB,
    `car_condition` VARCHAR(50) NOT NULL,
    `available` ENUM ('true','false') NOT NULL
);

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

INSERT INTO `cars` (`plate_number`,`category_id`,`car_condition`,`available`)
    VALUES ('Van123',1,'fsd',1),
        ('Sedan123',2,'dsffds',2),
        ('4x4',1,'fsdsfsd',2);

CREATE TABLE IF NOT EXISTS `employees`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(50),
    `last_name` VARCHAR(50) NOT NULL,
    `title` VARCHAR(50),
    `notes` TEXT
);

INSERT INTO `employees`(`last_name`)
    VALUES ('Ivan'),
        ('Petkan'),
        ('Pesho');

CREATE TABLE IF NOT EXISTS `customers`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `dirver_licence_number` VARCHAR(50) UNIQUE,
    `full_name` VARCHAR(50) NOT NULL,
    `address` VARCHAR(50),
    `city` VARCHAR(50),
    `zip_code` INT,
    `notes` TEXT
);

INSERT INTO `customers`(`full_name`)
    VALUES ('P.P'),
        ('D.D'),
        ('A.A');

CREATE TABLE IF NOT EXISTS `rental_orders`(
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `employee_id` INT NOT NULL,
    `customer_id` INT NOT NULL,
    `car_id` INT NOT NULL,
    `car_condition` VARCHAR(50) NOT NULL,
    `tank_level` DECIMAL,
    `kilometrage_start` INT(7),
    `kilometrage_end` INT(7),
    `total_kilometrage` INT(7),
    `start_date` DATETIME,
    `end_date` DATETIME,
    `total_days` INT(4),
    `rate_applied` DECIMAL,
    `tax_rate` DECIMAL,
    `order_status` VARCHAR(50),
    `notes` TEXT
);

ALTER TABLE `rental_orders`
    ADD CONSTRAINT `FK_orders_employees`
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`),
    ADD CONSTRAINT `FK_orders_customers`
    FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`),
    ADD CONSTRAINT `FK_orders_carsID`
    FOREIGN KEY (`car_id`) REFERENCES `cars`(`id`),
    ADD CONSTRAINT `FK_orders_carsCOND`
    FOREIGN KEY (`car_condition`) REFERENCES `cars`(`car_condition`);

INSERT INTO `rental_orders` (`employee_id`,`customer_id`,`car_id`)
    VALUES (1,1,1),
        (2,2,2),
        (3,3,3);